COUNTIF 2 Conditions using Defined Names

P

Paul Dennis

SLA_GROUP is a defined name for a COL A
NO_ROLL_NUM is a defined name for COL B

COUNTIF(SLA_GROUP,$C5) works but I'm trying to COUNTIF based on SLA_GROUP =
C% and also were NO_ROLL_NUM = 11.

any ideas?
 
B

Bob Phillips

=SUMPRODUCT(--(SLA_GROUP=$C5),--(NO_ROLL_NUM = 11))

those ranges must be the same size.
 
R

Ron Coderre

Try this:
Redefine the "refers to" ranges of your defined names to include only rows 1
through 65535, instead of the whole column and NOT the whole column. (eg
A1:A65365 instead of A:A)

Then try your formula again.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
P

Paul Dennis

This worked but I wanted to refer by defined name. It seems the only way
would be to combine both fields as a seperate field which is also something I
didn't want to do.
 
R

Ron Coderre

I'm sorry....I must not have been clear. Try redefining the range that is
referred to in your named ranges.

Insert>Names>Define
Select: SLA_GROUP
Refers to: change this from an entire column to rows 1 through 65535 of that
column)

Repeat for the NO_ROLL_NUM named range

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
P

Paul Dennis

Worked

=SUMPRODUCT((SLA_GROUP="C%")*(NO_ROLL_NUM=11))

where SLA_GROUP & NO_ROLL_NUM are defined fields from 1 to 65535 rather than
the full column.
 
Top