# Can countif work like sumif

#### donh

##### Board Regular
I have a summary sheet that lists out each date of the year in column A and another worksheet that holds all the detail (which also has the dates that the detail belongs to in column A)

Sumif works great for all the numbers summing I need by date but on the detial sheet I have a formula that will put "PROBLEM" on the rows that have an exception.

What I would like to do is make a count of the "PROBLEM"s by date on the summary sheet.

Countif would work for the entire sheet but I would have to build it by range of days in the detail which would be useless for next year since the data will not always be the same.

Can anyone help

Don

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
You can use something like this:

=SUMPRODUCT(--(B1:B100=A1),--(C1:C100="PROBLEM"))

I must be missing something because when I used sumproduct (which is a formula I have never tried) the result was #VALUE!

Not sure if it is because "Problem" is non-numeric

It might just be easier for me to make another column in my detial and instead of having the exception formula drop in PROBLEM to have it put a 1

Thanks

The fact that Problem is non-numeric shouldn't cause a problem because the SUMPRODUCT formula is using 0s and 1s (TRUE/FALSE values coerced to 1 and 0).

Can you post the SUMPRODUCT formula as you have implemented it?

Here is the formula:

=SUMPRODUCT(--('Detail 2'!\$B\$4:\$B\$65534=A353),--('Detail 2'!\$V\$4:\$V\$65534="PROB"))

Where column B holds all the dates and column V is where "PROB" will show if there is an exception in the logical formulas. Column A is all the dates of the year.

When I tried it again I did not get the #Value! error, but this time did get a count of -0- when in fact there should be 1.

Thanks

Are the dates in column A on the same sheet you have your formula on?

Yes - - - that is the summary sheet

Is PROB the only thing in the cell?

it is a formula that will either drop in "" or Prob

=IF(G8509="NB","",IF(G8509="ORT","",IF(D8509>0,IF(AND((N8509+O8509+P8509=0),(M8509+Q8509=0)),"PROB",""),"")))

kind of cumbersome but effective in finding errors before they surface elsewhere

Replies
1
Views
283
Replies
1
Views
260
Replies
5
Views
187
Replies
4
Views
146
Replies
3
Views
229

1,217,756
Messages
6,138,427
Members
450,136
Latest member
Tabako1960

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