how to count rows if 2 columns partially match

Q

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

The verbalized summary is that I want to search for rows which matc
"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 t
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?

TI
 
S

Spencer101

Qikstart;1606728 said:
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 matc
"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 t
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
Click to expand...

Care to post an example workbook
 
C

Claus Busch

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
Click to expand...

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
 
R

Ron Rosenfeld

Workbook attached.


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

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.
 
Q

Qikstart

Claus said:
Hi,

snip...

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


Regards
Claus Busch
Click to expand...

Claus, thanks so much, that COUNTIFS did the trick! The SUMPRODUCT als
works, but I have a wide variety of different prefix-text that I'll b
searching for, so the COUNTIFS covers all cases easily!

Again, THANKS

+-------------------------------------------------------------------
+-------------------------------------------------------------------
 
Top