If/CountIf

  • Thread starter Thread starter Christine Abbey
  • Start date Start date
C

Christine Abbey

Why is this not working?

=IF(January!F2:F2602="Adams,*",COUNTIF(January!
I2:I525,"Yes"),0)

The answer should be 1, but it is giving me 0.

There are 21 rows where "Adams,*" is true, one of which
where the I column is "Yes".

There are 134 rows total where the I Column is "Yes", but
I only want the one for each manager.

Help!
 
I'm assuming it's an array formula. If you kept the ranges
the same size you could do something like:

{=SUM(IF((F2:F2602="Adams,*")*(I2:I2602="Yes"),1,0))}

Excel Support Technician
www.canhelpyou.com
 
Christine,

Are you trying to treat * as a wildcard? SUM doesn't support wildcards.

You could try

=SUM(IF((LEFT(F2:F2602,6)="Adams,")*(I2:I2602="Yes"),1,0))

which is an array formula, so commit with Ctrl-Shift-Array, or

=SUMPRODUCT((LEFT(F2:F602,6)="Adams,")*(I2:I602="Yes"))

not an array form ula

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Christine!

You're using the wrong functions here.

Your formula is returning the correct result

=IF(January!F2:F2602="Adams,*",COUNTIF(January!I2:I525,"Yes"),0)

If Adams,* is in the range F2:F2602 then it is returning the number of
Yes in I2:I525

From what you say it looks like you need:

=SUMPRODUCT((January!$F$2:$F$2602="Adams,*")*(January!$I$2:$I$2602="Ye
s"))

You need matching arrays here though. [See below]

You might regard (January!$F$2:$F$2602="Adams,*") as being an implicit
IF function that returns TRUE or FALSE. Multiplying them together
coerces them to TRUE = 1, FALSE = 0. Only if both are TRUE will the
result be 1 and therefore cause an addition to the sum of the
products.

An alternative form is:

=SUMPRODUCT(--(January!$F$2:$F$2602="Adams,*"),--(January!$I$2:$I$2602
="Yes"))

Here, the coercion to 1 and 0 is done by the double negatives.

If you have a problem with the extension of the I range to match the F
range, then you can use:

=SUMPRODUCT((January!$F$2:$F$525="Adams,*")*(January!$I$2:$I$525="Yes"
))

It will yield the same result and will meet the case where you are
just not interested in cases where there is a match of "Adams,*" and
"Yes" below row 525
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top