SUMPRODUCT alternatives

Hi All,

I recently had the problem of using sumif function across a horizontal and vertical plane at the same time. I have used a sumproduct function to solve my difficulties and am getting the results I want. However the data size this formula is covering is large and the sumproduct function is taking up too much of my work PC processing power (have requested an upgrade - denied).

Please can you help me come up with an alternative formula that will:

a) Get the right results (sum the right cells)
b) Take less power to calculate
c) Be very easy to drag across a large number of cells

I have atached a sample data set with the formula in. I have removed the $ signs for ease but would reinsert these so the formula can be dragged around. The formula is in the highlighted cell.

Many Thanks

Thanks, I will try the array formula.

I can't get the pivot table to give me what i want - i will try using one again though

Try this in B17 Filled right/down

=SUMIF($A$4:$A$12,$A$16,INDEX($B$4:$G$12,0,MATCH(B$16,$B$2:$G$2,0)+($A17-1)))

Hi Jonmo1,

Thanks very much for your help, however my sample dataset was slightly too basic. The numbers do not run 1,2,3 in the real data which causes problems with the final part of your index calculation ($A17-1). I don't know enough about the index formula to work out an alternative. The real data set is shown below, can you still help?

Jan-14 Feb-14 Mar-14 Apr-14 May-14
3C22 730000 #REF!
3C22 730020
3C22 730080
3C22 730081
3C22 730200
3C22 378151
3C22 730001
3C90 730000
3C90 730020
3C90 730080
3C90 730081
3C90 730200
3C90 378151
3C90 730001


Many Thanks

However the dates dont look quite like that - they are spread out to the right as in the sample.

Thanks

Well, we can only work with what you show us.

Can you post a more realistic sample book?


What you just posted makes it look simpler.
Try just removing the +($A17-1) part

I have attached a more realistic workbook. New data is in tab 2.

The removal of the final section doesn't take into consideration the GL code line - it looks up 730000 each time.

Thanks again for you help

Are the numbers in Row 3 always going to be the same repeated accross each date?
i.e., will B3:F3 be exactly the same as G3:K3, and exactly the same as L3:P3

If so, try this in C23

=SUMIF($A$4:$A$15,$A23,INDEX($B$4:$P$15,0,MATCH(C$22,$B$2:$P$2,0)+(MATCH($B23,$B$3:$F$3,0)-1)))