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