SUMIF with Index Match

[psuLemon]

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'

1	Cycle 1	Cycle 1	Cycle 1	Cycle 1	Cycle 1	Cycle 1	Cycle 1	Cycle 1
2	Week 1	Week1	Week 2	Week 2	Week 3	Week 3	Week 4	Week4
3	Volume	% Delta	Volume	% Delta	Volume	% Delta	Volume	% Delta
4	2,325 <tbody> </tbody>	0%	1,835	-27%	2,275	19%	2,325	2%

<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

 

[godsaaint]

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

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

 

[psuLemon]

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.

 

[Russell Hauf]

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
	A	B	C
1	Cycle	Week	Volume
2	1	1	2,325
3	1	2	1,835
4	1	3	2,275
5	1	4	2,325
Sheet4