Nesting an IF within a COUNTIF

[Guest]

I need to count a range of cells based on whether another column has a 1 or a 0 in it...for example
A1 through A10 contains date
B1 through B10 contains the number of errors that occurred on the date in column
C1 through C10 contains either a one (1) or a zero (0) - based on another IF statement for another analysis function
I want to return the number of dates that have a certain number of errors (say 2) as long as the C column contains a one (1)
I have tried a COUNTIF(IF) but I keep getting an error...I think I am missing something so simple but I just can't figure it out.

 

[Norman Harker]

Hi Janice!

Assuming no double counting of dates you need the following:

=SUMPRODUCT(--(B1:B10=2),--(C1:C10=1))

The trouble is that COUNTIF does not allow multiple conditions. The
structures B1:B10=2 and C1:C10=2 return TRUE or FALSE the double negative
coreces these to 1 and 0. SUMPRODUCT then multiplys the results for each
pairing and adds that to the total. Thus only if both structures are TRUE
will the 1*1 result in a addition to the sum.

--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

I need to count a range of cells based on whether another column has a 1 or a 0 in it...for example:
A1 through A10 contains dates
B1 through B10 contains the number of errors that occurred on the date in column A
C1 through C10 contains either a one (1) or a zero (0) - based on another

IF statement for another analysis function.

I want to return the number of dates that have a certain number of errors

(say 2) as long as the C column contains a one (1).

I have tried a COUNTIF(IF) but I keep getting an error...I think I am

missing something so simple but I just can't figure it out.