# sumproduct or array formula for countif

#### JV0710

##### Active Member
Hi

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
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
That IS the right idea Peter and it works . . . Thank You

#### Charles Bushby

##### New Member
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
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

##### New Member
That works perfectly. Many thanks

Charles

Replies
0
Views
271
Replies
1
Views
248
Replies
2
Views
306
Replies
3
Views
561
Replies
1
Views
531

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.

### Which adblocker are you using?

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

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