OK, here's what I'm trying to do in Excel 2010... I may not know the correct terminology here, but I'll try to make it clear. I don't think my problem is difficult, but I don't know how to word a google search to figure out how to do this correctly:
Quick note: My explanation below may serve to confuse. The sampleData is relatively clear and shows both the result I want, and the result I'm getting.
I'm trying to tally sales data automatically, and I have 3 columns:
Column A - Sale Campaign
Column B - Sale Type (what was sold)
Column C - Location Code
My campaigns are split into location codes. For example, Campaign 1 is also split into location codes 5000/6000/7000.
My final report is formatted like this:
Campaign
TotalSales(in campaign)
Catsales (specific to campaign AND campaigncode, e.g. Cat sale in Campaign 1, location 5000)
Dogsales
Parrotsales
My problem is that I need to split up the sales as units, and some customers might buy more than one product at a time. For example, one customer might be a cat and a dog.
So, my column of salesdata looks something like this:
Cat
Dog
Cat, Dog
Cat, Parrot, Dog
What I need to do is to have excel search through this sales data and tell me how many times the word "Cat" appears, whether it's alone or paired with other products.
Right now I'm using =SUMPRODUCT(COUNTIFS(SaleCampaign,Campaign1,SaleType,List1,SaleLocationCode,List2)
List1 = A listing of all of the sales that I want to add a unit to my "cat" section i.e. (cat)(cat, dog)(Cat, dog, parrot)
List2 = A listing of location codes
The thing is, I can use the same formula just fine if I stick to 2 requirements... so, I can count all of my sales from campaign 1, area a or all of my cat sales in campaign 1, but I can't add a third criteria or I always get a result that is lower than expected.
If someone can help me with this problem or at least tell me how to word my question so I can figure out how to do it myself, I would sing their praises.
Sample Attached.
Bookmarks