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

Care to post an example workbook?

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

Workbook attached.

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.

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!