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
 

Some videos you may like

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
Joined
Jul 21, 2002
Messages
73,092
You can use something like this:

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

where A1 contains your date.
 

donh

Board Regular
Joined
May 7, 2002
Messages
151
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
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
May 7, 2002
Messages
151

ADVERTISEMENT

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
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Are the dates in column A on the same sheet you have your formula on?
 

donh

Board Regular
Joined
May 7, 2002
Messages
151

ADVERTISEMENT

Yes - - - that is the summary sheet
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Is PROB the only thing in the cell?
 

donh

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

Watch MrExcel Video

Forum statistics

Threads
1,113,918
Messages
5,545,025
Members
410,647
Latest member
bernardazar
Top