# SUMIF with Index Match

#### psuLemon

##### New Member
Hello everyone, I am hoping I can get some help again. I am trying to do a sum based on a few criteria, and I believe that the orientation of the criteria is causing my formula to fail. Standard SUMPRODUCT and SUMIFS haven't been able to produce the results (unless I hosed those up, which is always possible ). The closest I have been able to use is based on the below thread but it's adding things that it shouldn't (only trying to add volume, not % Delta).

http://www.mrexcel.com/forum/excel-questions/902838-sumif-index-match.html

Currently, my formula looks like that.

=SUMIF('Day 1'!\$E\$25:\$BV\$25,C\$2,INDEX('Day 1'!\$E\$29:\$BV\$32,0,MATCH("Volume",'Day 1'!\$E\$28:\$BV\$28,0)))

'Day 1'

1Cycle 1Cycle 1Cycle 1Cycle 1Cycle 1Cycle 1Cycle 1Cycle 1
2Week 1Week1Week 2Week 2Week 3Week 3Week 4Week4
3Volume % DeltaVolume% DeltaVolume% DeltaVolume% Delta
4
 2,325

<tbody>
</tbody>
0%1,835-27%2,27519%2,3252%

<tbody>
</tbody>

And this would repeat for 5 cycles.

On the results tab, I am trying to look at total volume over the cycle and then I can trend the difference between cycles (1 cycle = 4 weeks as shown above).

'Results'
 Cycle 1 Cycle 2 Cycle 3 Cycle 4 Cycle 5 14,520

<tbody>
</tbody>

The results of cycle 1 should be 14,520, but when I use the formula indicated above, it's giving me 14,519 because it's adding the % difference (-1%) into the equation. So what I am trying to determine, is how to modify the above formula or what formula I can use to total all volume in a given cycle.

TIA,

Lemon

Last edited:

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### godsaaint

##### Active Member
Unless I misunderstood what you need, this should do it(adjust the ranges):

=SUMIFS(range,A1:H1,"Cycle 1",A3:H3,"Volume")

Last edited:

#### psuLemon

##### New Member
Unless I misunderstood what you need, this should do it(adjust the ranges):

=SUMIFS(range,A1:H1,"Cycle 1",A3:H3,"Volume")
Well I definitely hosed something up because I tried that , and now it is working. Thank you for the help.

Last edited:

#### Russell Hauf

##### MrExcel MVP
Ok, I know this was answered, but I followed this when it was asked (I was busy), and I just couldn't help myself. I think that you will save yourself a ton of time in the long run if you restructure your data (see below). If you put the data in like this, it will be incredibly easy to calculate the delta, as well as create summations of your data using pivot tables (or filtering, etc.) - in my opinion, at least (fancy formulas are fun, but I try to avoid them if possible). For me, a little time on the front-end (making sure my data is structured in an efficient manner) saves a ton of time on the back-end.

That and a quarter will get you...well, nothing these days. Have fun!!

Book1
ABC
1CycleWeekVolume
2112,325
3121,835
4132,275
5142,325
Sheet4

Last edited:

Replies
5
Views
175
L
Replies
27
Views
2K
Replies
4
Views
346
Replies
2
Views
612
Replies
3
Views
320

1,195,659
Messages
6,010,957
Members
441,578
Latest member
brodiej

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back