Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 3
Default how to count rows if 2 columns partially match

I am trying to count rows based on following pseudo-code:
if (cellA&"*" matches colARange) AND "text" matches colBRange)
count

columns:
taska new
taskb closed
taskc closed
problema closed
problemb new
problemc closed

task
problem

The verbalized summary is that I want to search for rows which match "text*" AND "closed" or "new". Expected counts would be:
"text*" with "new": 1
"text*" with "closed": 2
"problem*" with "closed": 2
etc
I've tried multiple formulas, but I can't piece it all together to actually work:
=SUMPRODUCT((A$72:A$76=A81&"*")*(B$72:B$76="closed "))
=COUNT(IF((A72:A76=A81&"*")*(B72:B76="closed"),))
=ISNUMBER(SEARCH(A81,A72:A77))

Can someone help me put the pieces together correctly?

TIA
  #2   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by Qikstart View Post
I am trying to count rows based on following pseudo-code:
if (cellA&"*" matches colARange) AND "text" matches colBRange)
count

columns:
taska new
taskb closed
taskc closed
problema closed
problemb new
problemc closed

task
problem

The verbalized summary is that I want to search for rows which match "text*" AND "closed" or "new". Expected counts would be:
"text*" with "new": 1
"text*" with "closed": 2
"problem*" with "closed": 2
etc
I've tried multiple formulas, but I can't piece it all together to actually work:
=SUMPRODUCT((A$72:A$76=A81&"*")*(B$72:B$76="closed "))
=COUNT(IF((A72:A76=A81&"*")*(B72:B76="closed"),))
=ISNUMBER(SEARCH(A81,A72:A77))

Can someone help me put the pieces together correctly?

TIA
Care to post an example workbook?
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default how to count rows if 2 columns partially match

Hi,

Am Thu, 25 Oct 2012 15:35:01 +0000 schrieb Qikstart:

I am trying to count rows based on following pseudo-code:
if (cellA&"*" matches colARange) AND "text" matches colBRange)
count

columns:
taska new
taskb closed
taskc closed
problema closed
problemb new
problemc closed

task
problem


for task and closed try:
=SUMPRODUCT(--(LEFT(A72:A77,4)=A81),--(B72:B77="closed"))
or with excel version 2007 or 2010:
=COUNTIFS(A72:A77,A81&"*",B72:B77,"closed")


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #4   Report Post  
Junior Member
 
Posts: 3
Default

Quote:
Originally Posted by Spencer101 View Post
Care to post an example workbook?
Workbook attached.
Attached Files
File Type: zip counting multiple columns match with partial cell.zip (8.6 KB, 81 views)
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default how to count rows if 2 columns partially match

On Fri, 26 Oct 2012 01:07:11 +0000, Qikstart wrote:


Spencer101;1606729 Wrote:
Care to post an example workbook?


Workbook attached.


+-------------------------------------------------------------------+
|Filename: counting multiple columns match with partial cell.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=642|
+-------------------------------------------------------------------+


Given your sample workbook data:

C9 : New
B10: =COUNTIFS($A$1:$A$6,$A10&"*",$B$1:$B$6,B$9)

Select b10 and fill/drag down one; then select B10:B11 and fill/drag to the right. The references should self adjust.


  #6   Report Post  
Junior Member
 
Posts: 3
Default

Quote:
Originally Posted by Claus Busch View Post
Hi,

snip...

or with excel version 2007 or 2010:
=COUNTIFS(A72:A77,A81&"*",B72:B77,"closed")


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
Claus, thanks so much, that COUNTIFS did the trick! The SUMPRODUCT also works, but I have a wide variety of different prefix-text that I'll be searching for, so the COUNTIFS covers all cases easily!

Again, THANKS!
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 if two columns match different criteria Curt D. Excel Worksheet Functions 5 May 14th 23 07:44 PM
Userform combobox: best match on partially typed content? ker_01 Excel Programming 4 March 27th 09 03:56 PM
match week, count rows, sum rows? earls Excel Worksheet Functions 2 January 5th 07 03:34 AM
Need rows in Column A removed if they fully or partially match with any Column B row [email protected] Excel Discussion (Misc queries) 1 August 21st 05 11:41 PM
If two columns match then count one. How? Tried countif and sum . IMC Medrec Excel Worksheet Functions 1 February 14th 05 01:28 PM


All times are GMT +1. The time now is 11:29 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"