If/CountIf

[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!

 

[Kent]

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

 

[Bob Phillips]

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)

 

[Norman Harker]

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.