Count cells based on multiple criteria

[mhartman09]

I've been working on this for a while now and can't figure out how to get the desired result. I'll try to explain as best I can....<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I've got a spreadsheet used to track items given out by our salesmen. Row 1 contains column headers, column A lists the sales reps and column C lists items given out to certain customers. Here's a screenshot of a simplified version of my information:
<o></o>
<o></o>
<o></o>
<o>

</o>
<o></o>
<o>On a separate tab I'm trying to get totals broken down by sales rep and item type as shown:</o>
<o></o>
<o></o>
<o>

</o>
<o></o>
<o></o>
<o>I don't know what formula to use to calculate a total using two columns of criteria. I'm really stumped considering the "Items" column contains multiple items. I'd appreciate any help anyone can offer.</o>

 

[njimack]

=SUMPRODUCT(--(Sheet1!$A$2:$A$13=B$1),--(Sheet1!$C$2:$C$13=$A2))

 

[mhartman09]

Thanks for the response. It seems this formula will work, but it only counts the occurances of items that stand alone in the "Items" column. I need to include every occurance of each item, including those listed with other items in the same cell. This is what truly has me stuck.

 

[Desu Nota from Columbus]

I had this same formula:

=SUMPRODUCT(--(Sheet1!$A$2:$A$13=B$1),--(Sheet1!$C$2:$C$13=$A2))

and noticed it doesn't work with multiple items in one cell.

The second array needs to be altered to use the FIND function

 

[njimack]

I didn't notice that some of the entries in column C had more than one item. Try this:
=SUMPRODUCT(--(Sheet1!$A$2:$A$13=B$1),--(ISNUMBER(SEARCH($A2,Sheet1!$C$2:$C$13))))

 

[Desu Nota from Columbus]

=SUMPRODUCT(--(Sheet1!A2:A7=$B1)+--(ISNUMBER(FIND(A2,Sheet1!B2:B7))))


I just got this to work

Adjust as necessary

***EDIT****

Man, njimack is quick!


Use njimack's answer, it utilizes search which isnt case sensitive and has the correct absolute references!! Easy dragging of formula!

 

[mhartman09]

Thanks again for the responses. I tried using =SUMPRODUCT(--(Sheet1!$A$2:$A$13=B$1),--(ISNUMBER(SEARCH($A2,Sheet1!$C$2:$C$13)))), but still had trouble. However, I was able to get the correct results using the following formula:

=SUMPRODUCT((ISNUMBER(SEARCH(Sheet2!B$1,Sheet1!$A$2:$A$13)))*(ISNUMBER(SEARCH(Sheet2!$A2,Sheet1!$C$2:$C$13))))

 

[Desu Nota from Columbus]

Thanks again for the responses. I tried using =SUMPRODUCT(--(Sheet1!$A$2:$A$13=B$1),--(ISNUMBER(SEARCH($A2,Sheet1!$C$2:$C$13)))), but still had trouble. However, I was able to get the correct results using the following formula:

=SUMPRODUCT((ISNUMBER(SEARCH(Sheet2!B$1,Sheet1!$A$2:$A$13)))*(ISNUMBER(SEARCH(Sheet2!$A2,Sheet1!$C$2:$C$13))))

Didn't notice the difference between njimack's and mine until now.
=SUMPRODUCT(--(Sheet1!$A$2:$A$13=B$1)+--(ISNUMBER(SEARCH($A2,Sheet1!$C$2:$C$13))))

which is the same as using the *

 

[mhartman09]

I absolutely have to come back to this thread and look for more help. Although I have been able to get the desired counts using the formula above, it is lagging my entire Excel program so badly the program crashes if I have another Excel document open when I'm recalculating this document. I have switched to manual recalculation for now, but it still takes forever for the totals to update. Can anyone offer an alternative to using sumproduct that will still produce the same results. I have no idea what other formula I could use to count the number of occurrences of each text string as well as the sumproduct formula has, but without the lag.

 

[Aladin Akyurek]

Thanks again for the responses. I tried using =SUMPRODUCT(--(Sheet1!$A$2:$A$13=B$1),--(ISNUMBER(SEARCH($A2,Sheet1!$C$2:$C$13)))), but still had trouble. However, I was able to get the correct results using the following formula:

=SUMPRODUCT((ISNUMBER(SEARCH(Sheet2!B$1,Sheet1!$A$2:$A$13)))*(ISNUMBER(SEARCH(Sheet2!$A2,Sheet1!$C$2:$C$13))))

I absolutely have to come back to this thread and look for more help. Although I have been able to get the desired counts using the formula above, it is lagging my entire Excel program so badly the program crashes if I have another Excel document open when I'm recalculating this document. I have switched to manual recalculation for now, but it still takes forever for the totals to update. Can anyone offer an alternative to using sumproduct that will still produce the same results. I have no idea what other formula I could use to count the number of occurrences of each text string as well as the sumproduct formula has, but without the lag.

Shouldn't that be rather:

=SUMPRODUCT((Sheet1!$A$2:$A$13=$B1)*(ISNUMBER(SEARCH($A2,Sheet1!$C$2:$C$13))))

If you are on a 2007 system or later:

=COUNTIFS(Sheet1!$A$2:$A$13,$B1,Sheet1!$C$2:$C$13,"*"&$A2&"*")