sumproduct or array formula for countif

JV0710

Active Member
Joined
Oct 26, 2006
Messages
396
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Hi

Please help me with a formula for the following:

I have a spreadsheet with Months as headings across the columns and products down the rows.

The range: A4:G50. ( Including headings ).

The cells are populated with sales per product for each month listed.

In cell E3, I would like to count how many products had Zero Sales across the six months.

I can do it by separately summing the columns for each product and counting the products with zero sales for the six months - but I have other info in those cells and I do not want to change the spreadsheet.

Thanks

JVN
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,261
Office Version
  1. 365
Platform
  1. Windows
If this is the right idea, then just expand the ranges in the formula.

Excel Workbook
ABCDEFGH
32
4JanFebMarAprMayJun
5Product 1259843
6Product 2000000
7Product 3040000
8Product 4505613
9Product 5627942
10Product 6000000
11
Products
 

JV0710

Active Member
Joined
Oct 26, 2006
Messages
396
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
That IS the right idea Peter and it works . . . Thank You
 

Charles Bushby

New Member
Joined
Aug 10, 2005
Messages
42
A while ago someone asked how to use sumproduct. Answers provided were fine but I am asking if this can be taken as stage further.

In the original scenario the question was to use a formula that would take two columns of data, one a percent and the next a balance, multiply the two columns together and sum the result.

Example:
A B C
1 75% 100.00
2 90% 125.00
3 blank 400.00
4 blank 25.00
5 90% 1,200.00

The answer to the above was to be 1,692.50.

I would like to take this a stage further. Where the percentage field is left blank in column B of the above example I would like a user defined default to be inserted that is accessed from a named range. i.e. I don't want 100% to be the default but another percentage such as 15%.

How might one do that? Many thanks in advance

Charles Bushby
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
This assumes replacement value for blank cells to be in D1, change as necessary:

=SUM(IF(ISNUMBER(A1:A5),A1:A5,D1)*(B1:B5))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.
 

Forum statistics

Threads
1,181,102
Messages
5,928,065
Members
436,586
Latest member
latintxn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top