COUNTIF 2 Conditions using Defined Names
 

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?

COUNTIF 2 Conditions using Defined Names
 

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

those ranges must be the same size.

--

HTH

RP

"Paul Dennis" wrote in message
...
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?

COUNTIF 2 Conditions using Defined Names
 

Hi

Try something like:
=SUMPRODUCT((SLA_GROUP="C%")*(NO_ROLL_NUM=11))

Andy.

"Paul Dennis" wrote in message
...
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?

COUNTIF 2 Conditions using Defined Names
 

Getting error #NUM!

"Bob Phillips" wrote:

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

those ranges must be the same size.

--

HTH

RP

"Paul Dennis" wrote in message
...
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?

COUNTIF 2 Conditions using Defined Names
 

Getting error #NUM!


"Andy" wrote:

Hi

Try something like:
=SUMPRODUCT((SLA_GROUP="C%")*(NO_ROLL_NUM=11))

Andy.

"Paul Dennis" wrote in message
...
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?

COUNTIF 2 Conditions using Defined Names
 

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


"Paul Dennis" wrote:

Getting error #NUM!


"Andy" wrote:

Hi

Try something like:
=SUMPRODUCT((SLA_GROUP="C%")*(NO_ROLL_NUM=11))

Andy.

"Paul Dennis" wrote in message
...
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?

COUNTIF 2 Conditions using Defined Names
 

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.

"Ron Coderre" wrote:

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


"Paul Dennis" wrote:

Getting error #NUM!


"Andy" wrote:

Hi

Try something like:
=SUMPRODUCT((SLA_GROUP="C%")*(NO_ROLL_NUM=11))

Andy.

"Paul Dennis" wrote in message
...
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?

COUNTIF 2 Conditions using Defined Names
 

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

InsertNamesDefine
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


"Paul Dennis" wrote:

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.

"Ron Coderre" wrote:

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


"Paul Dennis" wrote:

Getting error #NUM!


"Andy" wrote:

Hi

Try something like:
=SUMPRODUCT((SLA_GROUP="C%")*(NO_ROLL_NUM=11))

Andy.

"Paul Dennis" wrote in message
...
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?

COUNTIF 2 Conditions using Defined Names
 

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.

"Ron Coderre" wrote:

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

InsertNamesDefine
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


"Paul Dennis" wrote:

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.

"Ron Coderre" wrote:

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


"Paul Dennis" wrote:

Getting error #NUM!


"Andy" wrote:

Hi

Try something like:
=SUMPRODUCT((SLA_GROUP="C%")*(NO_ROLL_NUM=11))

Andy.

"Paul Dennis" wrote in message
...
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?