# SUMPRODUCT alternatives

1. ## 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

2. ## Re: SUMPRODUCT alternatives

You can try this array formula but I don't think it is a better alternative to a SUMPRODUCT however, the PivotTable is.

=SUM(IF(\$A\$4:\$A\$12=\$A16,\$B\$2:\$G\$2=B16)*\$B\$4:\$G\$12)

3. ## Re: SUMPRODUCT alternatives

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

4. ## Re: SUMPRODUCT alternatives

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)))

5. ## Re: SUMPRODUCT alternatives

@Jonmo1, Very nice combination of SUMIF/INDEX/MATCH! Didn't think of it.

6. ## Re: SUMPRODUCT alternatives

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

7. ## Re: SUMPRODUCT alternatives

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

Thanks

8. ## Re: SUMPRODUCT alternatives

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

9. ## Re: SUMPRODUCT alternatives

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

10. ## Re: SUMPRODUCT alternatives

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)))

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1