Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
Quote:
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
|
|||
|
|||
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
|
|||
|
|||
Quote:
Again, THANKS! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count if two columns match different criteria | Excel Worksheet Functions | |||
Userform combobox: best match on partially typed content? | Excel Programming | |||
match week, count rows, sum rows? | Excel Worksheet Functions | |||
Need rows in Column A removed if they fully or partially match with any Column B row | Excel Discussion (Misc queries) | |||
If two columns match then count one. How? Tried countif and sum . | Excel Worksheet Functions |