+ Reply to Thread
Results 1 to 3 of 3

Array/Sumproduct issue. Trying to "count" text.

  1. #1
    Registered User
    Join Date
    10-24-2012
    Location
    Atlanta, USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Array/Sumproduct issue. Trying to "count" text.

    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.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Array/Sumproduct issue. Trying to "count" text.

    See attachment. The slightly modified formulas (yellow area) seem to match the manually calculated results (light blue)
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    10-24-2012
    Location
    Atlanta, USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Array/Sumproduct issue. Trying to "count" text.

    Perfect! Thanks so much. I've never used INDIRECT before, but it came up a lot while I was trying to figure this out. Seeing it actually in use clarified it for me.

    Using the wildcards to search for my Sale Type makes so much sense - wish I'd thought of it.

    As promised, I will sing your praises, even if no one knows what I'm talking about. I sincerely appreciate the help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1