Dynamic quarter to date sumif

[crave4excel]

Hey guys,
I'm been stuck on this formula for a while, please help.

The raw table below shows the fruit sale by month. Im trying to create a Month to Date formula that sums up the value of a specific fruit, based on the month selected. Specifically I can create a formula to sum Orange sale in March (which is 15 in this case), but how do I sum up sale of oranges from JANUARY TO MARCH (which is 42 in this case).
THANK YOU



[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MARCH[/TD]
[TD]APR[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ORANGES[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]ORANGES[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MARCH [/TD]
[TD]MONTH VALUE[/TD]
[TD]MTD VALUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORANGES[/TD]
[TD]15[/TD]
[TD]???[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


​[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

JAN​

[/TD]
[TD]

FEB​

[/TD]
[TD]

MARCH​

[/TD]
[TD]

APR​

[/TD]
[TD][/TD]
[TD]period[/TD]
[TD]

JAN​

[/TD]
[TD]

MARCH​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]APPLE[/TD]
[TD]

5​

[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]ORANGES[/TD]
[TD]

5​

[/TD]
[TD]

2​

[/TD]
[TD]

5​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]APPLE[/TD]
[TD]

6​

[/TD]
[TD]

4​

[/TD]
[TD]

2​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]ORANGES[/TD]
[TD]

42​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]ORANGES[/TD]
[TD]

10​

[/TD]
[TD]

10​

[/TD]
[TD]

10​

[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]APPLE[/TD]
[TD]

30​

[/TD]
[/TR]
</tbody>[/TABLE]


i4=SUMPRODUCT(($A$2:$A$5=$H4)*(MONTH($B$1:$E$1&1)>=MONTH($H$1&1))*(MONTH($B$1:$E$1&1)<=MONTH($I$1&1))*($B$2:$E$5))

copy down

 

[crave4excel]

Thanks Marziotullio! is there an alternative way to do this without using the 'month' formula? the format of the month in my raw data can't be detected by Month formula. I'm simplying trying to sum up all the values before the MARCH column.

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

JAN​

[/TD]
[TD]

FEB​

[/TD]
[TD]

MARCH​

[/TD]
[TD]

APR​

[/TD]
[TD][/TD]
[TD]period[/TD]
[TD]

<=​

[/TD]
[TD]

MARCH​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]APPLE[/TD]
[TD]

5​

[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]ORANGES[/TD]
[TD]

5​

[/TD]
[TD]

2​

[/TD]
[TD]

5​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]APPLE[/TD]
[TD]

6​

[/TD]
[TD]

4​

[/TD]
[TD]

2​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]ORANGES[/TD]
[TD]

42​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]ORANGES[/TD]
[TD]

10​

[/TD]
[TD]

10​

[/TD]
[TD]

10​

[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]APPLE[/TD]
[TD]

30​

[/TD]
[/TR]
</tbody>[/TABLE]


i4=SUMPRODUCT(($A$2:$A$5=$H4)*(COLUMN($B$1:$E$1)-COLUMN($B$1)+1<=3)*($B$2:$E$5)) copy down

 

[crave4excel]

Thanks! the formula doesn't work if the MONTH input cell is dynamic though, say, if i change MARCH to April, it would still give me the total of March to date.

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

JAN​

[/TD]
[TD]

FEB​

[/TD]
[TD]

MARCH​

[/TD]
[TD]

APR​

[/TD]
[TD][/TD]
[TD]period[/TD]
[TD]

<=​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]APPLE[/TD]
[TD]

5​

[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]ORANGES[/TD]
[TD]

5​

[/TD]
[TD]

2​

[/TD]
[TD]

5​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]APPLE[/TD]
[TD]

6​

[/TD]
[TD]

4​

[/TD]
[TD]

2​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]ORANGES[/TD]
[TD]

55​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]ORANGES[/TD]
[TD]

10​

[/TD]
[TD]

10​

[/TD]
[TD]

10​

[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]APPLE[/TD]
[TD]

36​

[/TD]
[/TR]
</tbody>[/TABLE]

i1=MONTH(E1&1) change e1: with b1 jan c1 febr d1 march e1 apr

i4=SUMPRODUCT(($A$2:$A$5=$H4)*(COLUMN($B$1:$E$1)-COLUMN($B$1)+1<=$I$1)*($B$2:$E$5)) copy down