Multiple Countif results
 

My heads hurting trying to sort this one out, and the answer will be so
obvious...but can anyone clear this up for me

I need to record multiple results based on set criteria being met i.e the
total number of Criteria A's for Staff Group B, Criteria A's for Staff Group
A and so on.

Staff Group A Criteria A
Staff Group B Criteria A
Staff Group A Criteria A
Staff Group B Criteria B
Staff Group B Criteria B

I've tried =SUMPRODUCT(--(Sheet1!B:B="Staff Group A")*(Sheet1!C:C="Criteria
A")) but keep getting #Num

Can anyone help
FtB

Multiple Countif results
 

SUMPRODUCT doesn't work with entire col references. Define the smallest range
size (identical size) which sufficient to cover, eg something like
(untested):
=SUMPRODUCT((Sheet1!B2:B100="Staff Group A")*(Sheet1!C2:C100="Criteria A"))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Fez the Blue" wrote:
My heads hurting trying to sort this one out, and the answer will be so
obvious...but can anyone clear this up for me

I need to record multiple results based on set criteria being met i.e the
total number of Criteria A's for Staff Group B, Criteria A's for Staff Group
A and so on.

Staff Group A Criteria A
Staff Group B Criteria A
Staff Group A Criteria A
Staff Group B Criteria B
Staff Group B Criteria B

I've tried =SUMPRODUCT(--(Sheet1!B:B="Staff Group A")*(Sheet1!C:C="Criteria
A")) but keep getting #Num

Can anyone help
FtB

Multiple Countif results
 

Hi,

you can not use B:B in a sumproduct function use B2:B1000 for eg. adjust for
your needs

hth
regards from Brazil
Marcelo

"Fez the Blue" escreveu:

My heads hurting trying to sort this one out, and the answer will be so
obvious...but can anyone clear this up for me

I need to record multiple results based on set criteria being met i.e the
total number of Criteria A's for Staff Group B, Criteria A's for Staff Group
A and so on.

Staff Group A Criteria A
Staff Group B Criteria A
Staff Group A Criteria A
Staff Group B Criteria B
Staff Group B Criteria B

I've tried =SUMPRODUCT(--(Sheet1!B:B="Staff Group A")*(Sheet1!C:C="Criteria
A")) but keep getting #Num

Can anyone help
FtB

Multiple Countif results
 

You're really close... Change the "*" in your formula to ",--" and you will
be there.
--
Jim


"Fez the Blue" wrote:

My heads hurting trying to sort this one out, and the answer will be so
obvious...but can anyone clear this up for me

I need to record multiple results based on set criteria being met i.e the
total number of Criteria A's for Staff Group B, Criteria A's for Staff Group
A and so on.

Staff Group A Criteria A
Staff Group B Criteria A
Staff Group A Criteria A
Staff Group B Criteria B
Staff Group B Criteria B

I've tried =SUMPRODUCT(--(Sheet1!B:B="Staff Group A")*(Sheet1!C:C="Criteria
A")) but keep getting #Num

Can anyone help
FtB

Multiple Countif results
 

Hello,

I think you want this:
=SUMPRODUCT(--(Sheet1!B:B="Staff Group A"), --(Sheet1!C:C="Criteria A"))

see if this works.

Cheers,

Scott

"Fez the Blue" wrote:

My heads hurting trying to sort this one out, and the answer will be so
obvious...but can anyone clear this up for me

I need to record multiple results based on set criteria being met i.e the
total number of Criteria A's for Staff Group B, Criteria A's for Staff Group
A and so on.

Staff Group A Criteria A
Staff Group B Criteria A
Staff Group A Criteria A
Staff Group B Criteria B
Staff Group B Criteria B

I've tried =SUMPRODUCT(--(Sheet1!B:B="Staff Group A")*(Sheet1!C:C="Criteria
A")) but keep getting #Num

Can anyone help
FtB

Multiple Countif results
 

Cheers Scott
Worked a treat
FtB

"Scott" wrote:

Hello,

I think you want this:
=SUMPRODUCT(--(Sheet1!B:B="Staff Group A"), --(Sheet1!C:C="Criteria A"))

see if this works.

Cheers,

Scott

"Fez the Blue" wrote:

My heads hurting trying to sort this one out, and the answer will be so
obvious...but can anyone clear this up for me

I need to record multiple results based on set criteria being met i.e the
total number of Criteria A's for Staff Group B, Criteria A's for Staff Group
A and so on.

Staff Group A Criteria A
Staff Group B Criteria A
Staff Group A Criteria A
Staff Group B Criteria B
Staff Group B Criteria B

I've tried =SUMPRODUCT(--(Sheet1!B:B="Staff Group A")*(Sheet1!C:C="Criteria
A")) but keep getting #Num

Can anyone help
FtB

Multiple Countif results
 

Worked a treat

?? Must admit I'm surprised you managed to get Scott's version of the
SUMPRODUCT -- which is still using entire col references -- to work. I
thought that was the cause of your #NUM! errors in the first place <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---