Can countif work like sumif

[donh]

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

 

[Andrew Poulsom]

You can use something like this:

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

where A1 contains your date.

 

[donh]

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]

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]

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]

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

 

[donh]

Yes - - - that is the summary sheet

 

[Scott Huish]

Is PROB the only thing in the cell?

 

[donh]

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