Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default count number of rows with 2 matching text cells

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default count number of rows with 2 matching text cells

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default count number of rows with 2 matching text cells

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 964
Default count number of rows with 2 matching text cells

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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 364
Default count number of rows with 2 matching text cells

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





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 747
Default count number of rows with 2 matching text cells

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count matching text in column Tom Excel Discussion (Misc queries) 1 September 29th 07 09:08 AM
Count matching text in two columns Bob Phillips Excel Discussion (Misc queries) 2 July 4th 07 12:00 PM
How to count the number of Excel cells with text formatted Italic phausman Excel Worksheet Functions 3 July 19th 06 04:32 PM
How to count matching text Duplicateman Excel Discussion (Misc queries) 6 November 26th 04 09:40 AM
how do I 'count' the number of cells with a text in red or black? Anjin Topeng Excel Worksheet Functions 2 November 4th 04 07:42 AM


All times are GMT +1. The time now is 09:49 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"