Sumproduct-report

**DMS** · 11-23-2009, 05:05 PM

Hey people,

Looking forward to being part of this forum.

I have a question related to the SUMPRODUCT formulas I am using.

I have a workbook with 8 seperate sheets. The first sheet is a data import from Sage ACT! with First Name, Surename, Industry, Job Title, County, Phone, Email, Opportunity Status, etc.

Something is going wrong with the calculation on my report sheets that I can´t figure out.

These "report" sheets look like this:

X - Interested XK - KAM Call

Aberdeenshire 49 2
Angus 21 0
Argyll 5 0

I have

Here is the formula I am using in the B3 (Aberdeenshire) X-Interested cell:

=SUMPRODUCT(--('RAW contact List'!L2:L9999="X - Phase Two Re-activation"),--('RAW contact List'!Y2:Y9999=Geographic!A3))

RAW contact List is the main sheet all the other sheets take data from. When I do a filter on the Opportunity Status on this sheet (for X - Phase Two Re-activation) , i have a total of 423 records.

However my report sheet reports 409 for the total of all the countys which is wrong.

The total should be a breakdown of the 423 records from the main sheet.

To create the seperate report sheet (countys), I did an advanced filter and made a new list with only unique values. This gave me my geographic names and other sheets for Industry, Job Title, etc.

Hopefully someone can point me in the right direction!

David

**NBVC** · 11-23-2009, 05:24 PM

Make sure that none of the entries in columns L or Y are mispelled or have extra spaces before or after...

**DMS** · 11-23-2009, 05:34 PM

Thanks for the reply NBVC!

Is there a quick way of checking the columns for extra spaces and verifying "proper case"?

The strange thing is when I use the same formula on the "Industry" sheet it reports 445 records (when again, it should be 423 but broken down into Industry).

=SUMPRODUCT(--('RAW contact List'!L2:L983="X - Phase Two Re-activation"),--('RAW contact List'!J2:J983=Industry!A3))

I need the formula to basically filter the Opportunity Status (L), then filter again on another field for "Industry", "Geographic", etc). Then count the total number of rows for each. Which shouldn´t be no more than 423, since there are only 423 records with Opportunity Status of "X - Phase Two Re-activation".

**NBVC** · 11-23-2009, 05:49 PM

Possible to see the sheet?

**DonkeyOte** · 11-23-2009, 06:04 PM

Also bear in mind if using XL2007 you may find you can utilise the new COUNTIFS function which is significantly more efficient than SUMPRODUCT (you may need to utilise wildcards - unclear)

**NBVC** · 11-23-2009, 06:08 PM

Originally Posted by DonkeyOte

Also bear in mind if using XL2007 you may find you can utilise the new COUNTIFS function which is significantly more efficient than SUMPRODUCT (you may need to utilise wildcards - unclear)

Unless you intend for others using 2003 to work with your workbook...

**DonkeyOte** · 11-23-2009, 06:10 PM

hence the word "may"...

**DMS** · 11-23-2009, 06:22 PM

Thanks for all the help!

I think i fixed the issue in the Industry sheet, because I now correctly get 423 as the total.

I can´t seem to pin point the issue in the other sheet though?

Is my formula correct for what i want to get?