Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Counting rows that meet conditions in multiple columns

I want to count how many people who attended an event hold a certain status.
I've tried a number of formulas, including:

=SUMPRODUCT((C2:C493="x")*(A2:A493="renewed"))
and =SUMPRODUCT(--(C2:C493="x"),--(A2:A493="renewed"))
(the "x" marks whether someone attended the event)

=SUMPRODUCT(IF(ISNUMBER(SEARCH("Asia Trail", B1:B493)),
IF(A2:A493="renewed",1)))
where "Asia Trail" is one of several text values in a cell

and I continue to get incorrect numbers. In other similar cells, I get zero
when just looking at the sheet I know the number has to be in the 100's.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 169
Default Counting rows that meet conditions in multiple columns

Odd, your second formula works just fine for me, which would make me think
that may be something is going on with the inputs. Are there extra spaces in
the "x" or in the "renewed"?
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"belleinhell" wrote:

I want to count how many people who attended an event hold a certain status.
I've tried a number of formulas, including:

=SUMPRODUCT((C2:C493="x")*(A2:A493="renewed"))
and =SUMPRODUCT(--(C2:C493="x"),--(A2:A493="renewed"))
(the "x" marks whether someone attended the event)

=SUMPRODUCT(IF(ISNUMBER(SEARCH("Asia Trail", B1:B493)),
IF(A2:A493="renewed",1)))
where "Asia Trail" is one of several text values in a cell

and I continue to get incorrect numbers. In other similar cells, I get zero
when just looking at the sheet I know the number has to be in the 100's.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 964
Default Counting rows that meet conditions in multiple columns

The SUMPRODUCT with the IF formula won't work, you would use

=SUMPRODUCT(--(ISNUMBER(SEARCH("Asia Trail",
B2:B493))), --(A2:A493="renewed"))

also the ranges need to be of equal size you are using B1:B493 and A2:A493

The other formulas should work so you must have leading trailing
spaces/hidden characters in your data



--


Regards,


Peo Sjoblom

"belleinhell" wrote in message
...
I want to count how many people who attended an event hold a certain
status.
I've tried a number of formulas, including:

=SUMPRODUCT((C2:C493="x")*(A2:A493="renewed"))
and =SUMPRODUCT(--(C2:C493="x"),--(A2:A493="renewed"))
(the "x" marks whether someone attended the event)

=SUMPRODUCT(IF(ISNUMBER(SEARCH("Asia Trail", B1:B493)),
IF(A2:A493="renewed",1)))
where "Asia Trail" is one of several text values in a cell

and I continue to get incorrect numbers. In other similar cells, I get
zero
when just looking at the sheet I know the number has to be in the 100's.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Counting rows that meet conditions in multiple columns

I tried your formula and still no success. I also tried:
=SUMPRODUCT((ISNUMBER(SEARCH("Asia Trail", B2:B493)))*(A2:A493="renewed"))
=SUMPRODUCT(--(ISNUMBER(SEARCH("Asia
Trail",B2:B493))),--(ISNUMBER(SEARCH(A2:A493,"renewed"))))
=SUMPRODUCT((ISNUMBER(SEARCH("Asia",B2:B493)))*(IS NUMBER(SEARCH(A2:A493,"renewed"))))
and none of those worked either. I'm going to try redoing the worksheet and
see if that does anything.

"Peo Sjoblom" wrote:

The SUMPRODUCT with the IF formula won't work, you would use

=SUMPRODUCT(--(ISNUMBER(SEARCH("Asia Trail",
B2:B493))), --(A2:A493="renewed"))

also the ranges need to be of equal size you are using B1:B493 and A2:A493

The other formulas should work so you must have leading trailing
spaces/hidden characters in your data



--


Regards,


Peo Sjoblom

"belleinhell" wrote in message
...
I want to count how many people who attended an event hold a certain
status.
I've tried a number of formulas, including:

=SUMPRODUCT((C2:C493="x")*(A2:A493="renewed"))
and =SUMPRODUCT(--(C2:C493="x"),--(A2:A493="renewed"))
(the "x" marks whether someone attended the event)

=SUMPRODUCT(IF(ISNUMBER(SEARCH("Asia Trail", B1:B493)),
IF(A2:A493="renewed",1)))
where "Asia Trail" is one of several text values in a cell

and I continue to get incorrect numbers. In other similar cells, I get
zero
when just looking at the sheet I know the number has to be in the 100's.




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
Counting characters in multiple rows when rows meet specific criteria news.virginmedia.com Excel Worksheet Functions 3 June 28th 08 09:03 PM
Counting # of cells with that meet criteria in two columns Scott at Medt. Excel Worksheet Functions 7 September 19th 07 09:49 PM
count pieces of records meet conditions in different columns Amy Excel Worksheet Functions 1 July 19th 07 10:39 AM
Hiding rows that meet multiple conditions in excel KBV Excel Discussion (Misc queries) 1 July 4th 06 10:14 PM
counting cells in a data range that meet 3 specific conditions bekah7 Excel Discussion (Misc queries) 3 October 1st 05 06:21 AM


All times are GMT +1. The time now is 10:01 PM.

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"