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 Govier wrote:
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/f...es/pivot1.html

Hi all...

[quoted text clipped - 32 lines]

Matt