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

alecabral08 · #1 05-29-2008, 12:27 PM

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

NBVC · #2 05-29-2008, 12:37 PM

In E5:

Code:

=SUMPRODUCT(--ISNUMBER(MATCH(DataTable!$E$3:$E$9,SourceCodes!$D$5:$D$10,0)),DataTable!$F$3:$F$9)

alecabral08 · #3 05-29-2008, 03:00 PM

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!

NBVC · #4 05-29-2008, 03:13 PM

Just delete the last argument from each Sumproduct() formula.

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

alecabral08 · #5 05-29-2008, 05:19 PM

Thanks a lot, that worked!!!

alecabral08 · #6 07-21-2008, 04:47 PM

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

NBVC · #7 07-21-2008, 04:59 PM

Just a matter of adding an extra condition to each Sumproduct() formula...

see attached.

alecabral08 · #8 07-21-2008, 05:26 PM

Thanks! I was missing one part, that explains why it wasn´t working.

Welcome to the Excel Forum

Forum Statistics