+ Reply to Thread
Results 1 to 8 of 8

Count multiple rows (text) with multiple criteria (text)

Hybrid View

  1. #1
    Registered User
    Join Date
    05-29-2008
    Posts
    14

    Count multiple rows (text) with multiple criteria (text)

    Hi there!

    Hereīs the thing: I have a table/worksheet (DataTable) where I track every business opp that our salesmen insert in our crm. One of the columns of this table, named "source code" holds the name of the event that generated the business lead. I.e: "breakfast at hilton hotel". There are multiple rows that in this column hold this value, as well as others hold others. (I will attach an example, sorry if Iīm not clear enough).

    Hereīs the thing: in another worksheet (SourceCodes)I have another table holding the entire list of available source codes, by event type. This means that each source code is related to one specific event type. I.e: social events, launchs, etc.

    Now, there is yet another worksheet (Stats) where my table is empty and here is my problem. I need to count rows in my first worksheet/table and determine, matching them to the data in the second sheet (validating data) grouping them by event type.

    So, basically in this last table I need to see for example how many social events we hosted, (i donīt need the detail on how many breakfasts, cocktails and such we did, but the sum of all of them).

    What formula should I try? SUM, SUMIF, DCOUNTA and such havenīt been of much help up to now...

    Thanks!!!

    Ale
    Attached Files Attached Files

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    In E5:
    =SUMPRODUCT(--ISNUMBER(MATCH(DataTable!$E$3:$E$9,SourceCodes!$D$5:$D$10,0)),DataTable!$F$3:$F$9)
    in E6:

    =SUMPRODUCT(--ISNUMBER(MATCH(DataTable!$E$3:$E$9,SourceCodes!D11:D14,0)),DataTable!$F$3:$F$9)
    in E7:

    =SUMPRODUCT(--ISNUMBER(MATCH(DataTable!$E$3:$E$9,SourceCodes!$D$15:$D$18,0)),DataTable!$F$3:$F$9)
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    05-29-2008
    Posts
    14
    hi! Thanks, though this formula adds the values at the U$S column. Thatīs fine, but what I need to know is how many of my opportunities where created from each event, so what I need is to count the events codes, not add the values.

    Any ideas?

    Thanks!

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Just delete the last argument from each Sumproduct() formula.

    i.e. delete: ,DataTable!$F$3:$F$9

  5. #5
    Registered User
    Join Date
    05-29-2008
    Posts
    14
    Thanks a lot, that worked!!!

  6. #6
    Registered User
    Join Date
    05-29-2008
    Posts
    14
    Hi again

    As this formula yo helped me with worked wonders in my dashboard, I decided to try and expand itīs use though it seems I am not good enough yet.

    In the same example I attached before, I added a "created" column (DataTable sheet) which indicates in which Fiscal Year this opportunity was created in our system.

    In the stats sheet I added 4 more columns: FY08 (U$S and quantity) and FY09 (U$S and quantity). I should be able to split the original values into these categories.

    i.e.:
    E5 should be the sum of G5 and I5.
    F5 should be the sum of H5 and J5.

    I know how to sum of course, but the question is what would be the formulas for G, I, H and J? I canīt use the original formulas that now count and sum in E and F, as those have no information on the created fiscal year.

    Thanks

    Ps: Iīm attaching the modified example
    Attached Files Attached Files

+ 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