Using SUMPRODUCT...

M

matt_n

Hi all...

Hoping you can help...

I'm fairly new to Excel but have been picking up bits and pieces (mainly from
this forum!!!)....

I've got a worksheet which has 20 columns and usually around 500 rows of data.
I am trying to use it to total the number of referrals received from specific
sources and for specific reasons...as there are over 15 sources and 5 reasons,
I have had to try to come up with a formula which will 'pick and choose' the
correct elements. To do this I am using the following forumla:

=SUMPRODUCT(--($W$2:$W$1000="Criteria 1"),--(!$V$2:$V$1000="Criteria 2"),--(
$O$2:$O$1000<>"Branches"))

This works very well, and returns the actual volume of hits. However, I am
also trying to incorporate values. For each row, there will be a monetary
value (in coumn N). What I would like to do, is modify the formula I am
using so that it will total the values of the rows which meet my criteria.

Hope this makes sense?

Any help would be gratefully received

Matt
 
R

Roger Govier

Hi Matt


Just add in the column N to your Sumproduct formula as below
=SUMPRODUCT(--($W$2:$W$1000="Criteria 1"),
--(!$V$2:$V$1000="Criteria 2"),
--($O$2:$O$1000<>"Branches"),
--($N$2:$N$1000))

Strictly speaking, you do not need the --( ) around the range containing
your amounts to be summed, as these are numeric values already, but it
is easier to see the general case by including them.

All the other sets of conditions are tests of True or False and the
double unary minus --( ) is coercing them into 1 for True and 0 for
False so they can be multiplied together in the array created by
Sumproduct.
For each row, if all other conditions are met then you will have
1 x 1 x 1 x Value in N
Any 0 with the other conditions will cause the whole row to be 0.

You might like to consider the use of a Pivot Table with this data in
order to be able to quickly change or select different criteria for
analysis. For more help on setting up Pivot tables go to Debra
Dalgleish's site
http://www.contextures.com/tiptech.html
and scroll to the section on Pivot Tables
or Mike Alexander's site
http://www.datapigtechnologies.com/ExcelMain.htm
http://www.datapigtechnologies.com/flashfiles/pivot1.html
 
M

matt_n

Roger

Thank you very much for your help!

Something which has had me confused for so long can be solved so very very
quickly - and simply!! Think its indicative of my Excel experiences...!!
But hey, lilve and learn!

I have tried using pivot tables, but I am drawing data from quite a few
different sources, plus I have monthly files which then have data transferred
to an annual file, so I have found them to be a bit cumbersome. My present
attempt spreads across 5 worksheets, with each containing 8 pivot tables!!
Thought I would experiment with this method as it cuts out all the other
sheets!

Thanks anyways, and your help is very much appreciated

Matt

Roger said:
Hi Matt

Just add in the column N to your Sumproduct formula as below
=SUMPRODUCT(--($W$2:$W$1000="Criteria 1"),
--(!$V$2:$V$1000="Criteria 2"),
--($O$2:$O$1000<>"Branches"),
--($N$2:$N$1000))

Strictly speaking, you do not need the --( ) around the range containing
your amounts to be summed, as these are numeric values already, but it
is easier to see the general case by including them.

All the other sets of conditions are tests of True or False and the
double unary minus --( ) is coercing them into 1 for True and 0 for
False so they can be multiplied together in the array created by
Sumproduct.
For each row, if all other conditions are met then you will have
1 x 1 x 1 x Value in N
Any 0 with the other conditions will cause the whole row to be 0.

You might like to consider the use of a Pivot Table with this data in
order to be able to quickly change or select different criteria for
analysis. For more help on setting up Pivot tables go to Debra
Dalgleish's site
http://www.contextures.com/tiptech.html
and scroll to the section on Pivot Tables
or Mike Alexander's site
http://www.datapigtechnologies.com/ExcelMain.htm
http://www.datapigtechnologies.com/flashfiles/pivot1.html
Hi all...
Click to expand...
[quoted text clipped - 32 lines]
Matt
Click to expand...
Click to expand...
 
Top