I'm trying to get a count of how many times a product is shipped.
So, in Sheet 1 Column A are the products, column B it says shipped. I'd like to be able to get the total of how many times a product shipped in Sheet 2. Although i figured out a way to do this --- =SUM((Sheet1!B3:B62="Product")*Sheet1!C3:C62="Shipped")), the problem i have is that I'd like it to pick up one word of the product even though there are other words in the referenced cells (e.g. Red apples and Green apples, and i just want it to find the word apples).
Any help with this would be great!
Thanks
Hi,
How about something like this
=SUMPRODUCT((ISNUMBER(FIND("apples",Sheet1!B3:B62)))*(Sheet1!C3:C62="Shipped"))
Last edited by sweep; 07-01-2011 at 05:13 AM. Reason: can't use wildcards!
Please disregard anything in the above post. It may well have been edited without my consent, as has been the case with several posts and threads recently.
Thanks!
That worked!
Hello,
You are using Excel 2010. So you can simply use,
=COUNTIFS(Sheet1!B:B,"*apple*",Sheet1!C:C,"Shipped")
Which is faster than SUMPRODUCT.
HTH; Haseeb
If your problem is solved, please say so clearly, and mark your thread as Solved:
Forum Rules & How to Mark a thread as SOLVED
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks