sumproduct or array formula for countif

[JV0710]

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

 

[Peter_SSs]

If this is the right idea, then just expand the ranges in the formula.

Excel Workbook
	A	B	C	D	E	F	G	H
3					2
4		Jan	Feb	Mar	Apr	May	Jun
5	Product 1	2	5	9	8	4	3
6	Product 2	0	0	0	0	0	0
7	Product 3	0	4	0	0	0	0
8	Product 4	5	0	5	6	1	3
9	Product 5	6	2	7	9	4	2
10	Product 6	0	0	0	0	0	0
11
Products

 

[JV0710]

That IS the right idea Peter and it works . . . Thank You

 

[Charles Bushby]

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]

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.

 

[Charles Bushby]

That works perfectly. Many thanks

Charles