I have a spreadsheet that contains installation coordinators names, the corresponding install branches, and if the orders is closed or open. I would like to have one tab with the raw data and then one tab for each install branch. On the tab for the install branch, I would like to list out the coordinators and count how many orders each one has open and closed (I don't know if listing the names can be automated or not, but if it could, please let me know how to do that). My problem arises because the column containing the information regarding whether the order is open or closed also contains other wording - such as the reason that order was closed, or the reason the order is still pending. Is there a way to use wildcards in a countifs formula (so that the formula will count any cell that has the word "closed" somewhere in it)? Also, is there a way to use a not equal comparison in a countifs formula (so that the formula will count any cell that does not contain the word "closed")? Or is there a better way to accomplish this? Any advice you can provide would be greatly appreciated.

You can use wildcards with COUNTIF function.


e.g. =COUNTIF(A1:A100,"*closed*")

that counts cells in A1:A100 that contain the word "closed" amongst strings of text.

=COUNTIF(A1:A100,"<>*closed*")


will count cells that do not contain the word "closed". Note: this will also count cells that are totally blank too, as they do not contain "closed" and so are counted.... You would need SUMPRODUCT formula to ignore blanks, or if you have XL2007 or later, then you could employ COUNTIFS.


If you need further help with your workbook, it would be best if you post a sample workbook with confidential data removed/replaced... and advise what version of Excel you are using.

That's what I needed. Thank you very much for your help.

I do have one more question. In the raw data that I have, a coordinator can be listed many times, and in some cases with more than one install branch. I was just wondering if there is a formula that will filter that raw data and list all the coordinators associated with an install branch once.

Again, it would be easier to follow you if you post a sample workbook (it doesn't have to contain a lot of data...just some relevant representative samples) and an sample of the expected output you want.

Attached is a sample worksheet. The raw data tab, shows the install branch, coordinator, and status. The other three tabs show the coordinators for each branch. I would like a formula to list the coordinators like that for me (as the acutal spreadsheet has 30,000 + lines and about 50 different install branches).

I am working in Office 2007.

Try this:

In the raw data sheet, add a helper column with formula in D2:

=A2&"_"&COUNTIF(A$2:A2,A2)

copied down

Then insert a row at the top of each Install Branch sheet and in A1 of each sheet insert the Install Branch number as per you column A in the raw data.

Then select the first install branch tab, hold the shift key and select the last install branch sheet. This selects all the sheets except the raw data sheet.

In B1 of the install branch sheet that is active enter formula:

=COUNTIF('Raw Data'!A:A,A1)

Then in A3 enter:

=IF(ROWS($A$1:$A1)>$B$1,"",INDEX('Raw Data'!$B:$B,MATCH($A$1&"_"&ROWS($A$1:$A1),'Raw Data'!$D:$D,0)))

and copy down as far as you want.

Now select the raw data sheet to deselect the others... and go through each sheet to see the results.

That does almost exactly what I need it to. Is there a way to eliminate the duplicate values? For example for install branch 1234, smithb is listed twice. I would like him listed only once.

Try changing the formula in D2 of Raw Data sheet to:

=IF(SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2))=1,A2&"_"&COUNTIF(D$1:D1,A2&"*")+1,"")

or if you have Excel 2007 or later use:

=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,A2&"_"&COUNTIF(D$1:D1,A2&"*")+1,"")

and copy down

Then change formula in B1 of each of the Install Branch sheets to:

=COUNTIF('Raw Data'!D:D,$A$1&"*")

That works perfectly. Thank you very much for your help!