Hi Grant

Compared with the original posting,column G now appears to contain your
values to be summed

=SUMPRODUCT(--($A$2:$A$100="criteria1"),
--($B$2:$B$100="criteria2"),
--($C$":$C$100="criteria3),
--($D$2:$D$100="criteria4"),
--($E$2:$E$100="criteria5"),
--($F$2:$F$100=1),
$G2:$G100)

This would test the case where Month is 1 (F2:F100=1), and that there
were criteria in the other 5 columns.
Omit the section relating to any one of the columns to exclude that from
the result.
If you are going to do this for each month, with 1 in H1, 2 in H2 etc,
change to
--($f$2:$F$100=H1) and copy down.
To deal with anything from 1 to 5 criteria, you would need to copy
across, with each formula containing an additional criteria, so you
would have a matrix of 60 cells with the differing results.

--
Regards

Roger Govier


"Grant Reid" wrote in message
...
Hi

I am still bumping my head with this one. I've had a look at
http://xldynamic.com/source/xld.SUMPRODUCT.html as Roger suggested. It
certainly seems as if Sumproduct could be the answer, but for the life
of me I can't figure how to apply it to what I'm trying to
accomplish.

Just to summarise again what I'm trying accomplish. I have my data
residing in A6:G100
A B C D E F
G
Row 5 Year Acc Clnt Prod Rev Month Amount

I need to sum the numeric data in G5:G100 that meets criteria I have
in A2:E2
A B C D E
Row 1 Year Acc Clnt Prod Rev
Row 2 xxxx xxxx xxxx xxxx xxxx

but I need to do this 12 times - one result for every month in 12
different cells - assume results go H1:H12. The data in the month
column, F6:F100 is numeric, obviously ranging 1 through to 12.

To complicate matters even further, sometimes I will have number of
criteria permutations in A2:E2. Sometimes I will have all five
criteria, sometimes three, sometimes four, somtimes two, sometimes one
and even on occasion, none. Below is an example of where I
have three criteria
A B C D E
Row 1 Year Acc Clnt Prod Rev
Row 2 xxxx xxxx xxxx

and one criteria
A B C D E
Row 1 Year Acc Clnt Prod Rev
Row 2 xxxx

etc etc

All of this has to be accomplished without resorting to array
formulae, VBA or functionality such as pivot tables.

Any further help will be much appreciated

Kind Regards - Grant