Can countif work like sumif

donh

Board Regular
Joined
May 7, 2002
Messages
151
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"))

where A1 contains your date.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
Are the dates in column A on the same sheet you have your formula on?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top