# 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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### Andrew Poulsom

##### MrExcel MVP
You can use something like this:

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

#### donh

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

#### Scott Huish

##### MrExcel MVP
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?

#### donh

##### Board Regular

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

#### Scott Huish

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

#### donh

##### Board Regular

Yes - - - that is the summary sheet

#### Scott Huish

##### MrExcel MVP
Is PROB the only thing in the cell?

#### donh

##### Board Regular
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
8
Views
85
Replies
2
Views
74
Replies
4
Views
75
Replies
0
Views
164
Replies
3
Views
50