hELP WITH MUTIPLE SUMIF AND CUMULATIVE SUM

[muffinson]

Yesterday I posted this and I was so thankful to get a reply which answered my question

A B C D E F
1 Month ?
2
3 Jan Feb Mar Apr May MTD
4 10 14 15 9 8 ?


I want to have a cumulative sum formula in cell f4, which would add up the figures in row 4 depending on the date I entered in cell B2.

For e.g. if I entered Mar in B1, my MTD figure in F4 would = 39, the Sum of Jan, Feb & Mar.

Answer =SUM(C19:INDEX(C19:N19,MATCH($B$3,$C$8:$N$8,0)))

Today’s question is a little trickier.

A B C D
1
2 CODE JAN FEB MAR
3 10 2 3 4
4 11 2 2 1
5 12 4 2 1
6 13 5 4 6
7
8
9 month?
10
11 CODE MTD
12 10 ?
13 11
14 12
15 13

I want to cumulative sum a range if the codes on one sheet match the codes on another and then for the sum to return a cumulative based on what month I put in cell A9

eg IF THE MONTH ENTERED IS FEB THE SUM RETURNED IS 5 IN CELL B12

The formula is a combination of =SUM(B3:INDEX(B3:D3,MATCH($A$9,$B$2:$D$2,0))) and =SUMIF($A$3:$A$6,$A12,B3:D6)

can any one help please.
:x

 

[Andrew Poulsom]

In B12:

=SUM(INDEX($B$3:$D$6,MATCH($A12,$A$3:$A$6,0),1):INDEX($B$3:$D$6,MATCH($A12,$A$3:$A$6,0),MATCH($A$9,$B$2:$D$2,0)))

copied down.

 

[muffinson]

Thank you

Thank you so much! It works a treat... You have saved me so much time..

 

[muffinson]

Oh one more question last one I promise...

What would the formula be if I didnt want a cumulative total and just wanted the month?

 

[muffinson]

Help

 

[Andrew Poulsom]

That's the second part of the formula I posted:

=INDEX($B$3:$D$6,MATCH($A12,$A$3:$A$6,0),MATCH($A$9,$B$2:$D$2,0))