I have a data sheet with data listing users and a product group. I need to
count the number of occurances where the user and group match. so on the
second sheet i need to fill cells with the count of "IF !A:A = "X" and !B:B =
"Y"". I have been trying countif and sumproduct with no luck. I can get the
sum of both individual matches but not when the combination matches.

Any ideas?

Thanks
Scott

Maybe

=SUMPRODUCT((Sheet1!A1:A30="xx")*(Sheet1!B1:B30="y y"))

Mike

"smcmoran" wrote:

I have a data sheet with data listing users and a product group. I need to
count the number of occurances where the user and group match. so on the
second sheet i need to fill cells with the count of "IF !A:A = "X" and !B:B =
"Y"". I have been trying countif and sumproduct with no luck. I can get the
sum of both individual matches but not when the combination matches.

Any ideas?

Thanks
Scott

Thanks but I get zero for the result, no error, but no count. I should get
a result of 15 with the example data I'm using.

Scott

"Mike H" wrote:

Maybe

=SUMPRODUCT((Sheet1!A1:A30="xx")*(Sheet1!B1:B30="y y"))

Mike

"smcmoran" wrote:

I have a data sheet with data listing users and a product group. I need to
count the number of occurances where the user and group match. so on the
second sheet i need to fill cells with the count of "IF !A:A = "X" and !B:B =
"Y"". I have been trying countif and sumproduct with no luck. I can get the
sum of both individual matches but not when the combination matches.

Any ideas?

Thanks
Scott

It should work

maybe you have trailing or leading spaces

=SUMPRODUCT(--(TRIM(A2:A20)="X"),--(TRIM(B2:B20)="Y"))

if that doesn't work you must have invisible html characters

--


Regards,


Peo Sjoblom

"smcmoran" wrote in message
...
Thanks but I get zero for the result, no error, but no count. I should
get
a result of 15 with the example data I'm using.

Scott

"Mike H" wrote:

Maybe

=SUMPRODUCT((Sheet1!A1:A30="xx")*(Sheet1!B1:B30="y y"))

Mike

"smcmoran" wrote:

I have a data sheet with data listing users and a product group. I
need to
count the number of occurances where the user and group match. so on
the
second sheet i need to fill cells with the count of "IF !A:A = "X" and
!B:B =
"Y"". I have been trying countif and sumproduct with no luck. I can
get the
sum of both individual matches but not when the combination matches.

Any ideas?

Thanks
Scott

Try using

=SUM(--(A1:A30=B1:B30))

This is an array formula, so you need to use Ctrl+Shift+Enter rather than
just Enter when you first enter the formula or whenever you edit it.

Stephen

"smcmoran" wrote in message
...
Thanks but I get zero for the result, no error, but no count. I should
get
a result of 15 with the example data I'm using.

Scott

"Mike H" wrote:

Maybe

=SUMPRODUCT((Sheet1!A1:A30="xx")*(Sheet1!B1:B30="y y"))

Mike

"smcmoran" wrote:

I have a data sheet with data listing users and a product group. I
need to
count the number of occurances where the user and group match. so on
the
second sheet i need to fill cells with the count of "IF !A:A = "X" and
!B:B =
"Y"". I have been trying countif and sumproduct with no luck. I can
get the
sum of both individual matches but not when the combination matches.

Any ideas?

Thanks
Scott

try this

=COUNT(IF((A1:A30="x")*(B1:B30="y"),))



On Sep 29, 11:05*am, smcmoran
wrote:
I have a data sheet with data listing users and a product group. *I need to
count the number of occurances where the user and group match. *so on the
second sheet i need to fill cells with the count of "IF !A:A = "X" and !B:B =
"Y"". *I have been trying countif and sumproduct with no luck. *I can get the
sum of both individual matches but not when the combination matches.

Any ideas?

Thanks
Scott