Figures based on month selection

[Salamullah]

Hi Experts - Please give any formula

I want to get the figures in row 3 for each month based on selection of month in cell A1 as per the given example below, in Mar and Apr figures are showing zero
Suppose if I select Mar in A1 only Apr should show zero rest of the months should take sum from array F6:G11

[TABLE="width: 500"]
<tbody>[TR]
[TD]Feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD]200[/TD]
[TD]400[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Month[/TD]
[TD]Sales[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jan
[/TD]
[TD]200
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Feb[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Feb[/TD]
[TD]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mar[/TD]
[TD]500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mar[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Apr[/TD]
[TD]1000[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[PCL]

if I select Mar in A1 only Apr should show zero and not May, June etc ...?

 

[PCL]

Let's assume data Month/sales is in range K2 to L15
Months name are in range O1 : O12 = Jan, Feb, MAr ...Nov, Dec
B4 = =IF(MATCH(B2,$O$1:$O$12,0)<=MATCH($A$1,$O$1:$O$12,0),SUMPRODUCT(($K$2:$K$15=B2)*($L$2:$L$15)),0)
Copy and paste until the last column of display .. E4

 

[Salamullah]

Excellent !! PCL Formula given is perfect thanks

Best Regards.

 

[Salamullah]

I have used the below with your given formula, only the issue its giving FALSE instead of Zero. What I am missing ?

[TABLE="width: 480"]
<colgroup><col><col span="5"></colgroup><tbody>[TR]
[TD="align: left"]Feb[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: left"]Jan[/TD]
[TD="align: left"]Feb[/TD]
[TD="align: left"]Mar[/TD]
[TD="align: left"]Apr[/TD]
[TD="align: left"]May[/TD]
[/TR]
[TR]
[TD="align: left"]Sales[/TD]
[TD="align: left"] 1,239,737[/TD]
[TD="align: left"] 2,793,474[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(MATCH(E5,$E$5:$P$5,0)<=MATCH($A$3,$E$5:$P$5,0),SUMIFS(Table_Query_from_TB3[SDITVL],Table_Query_from_TB3[Month],E$5,Table_Query_from_TB3[SDAN8],Sales!$F$8))

[/FONT]

 

[higrm]

=IF(MATCH(E5,$E$5:$P$5,0)<=MATCH($A$3,$E$5:$P$5,0),SUMIFS(Table_Query_from_TB3[SDITVL],Table_Query_from_TB3[Month],E$5,Table_Query_from_TB3[SDAN8],Sales!$F$8),0)

 

[Salamullah]

Go it thanks

 

[PCL]

It seems was missing the end of the formula when you copied it...!
You know that instead of using text for month name ( e.g. Jan, Feb ...) you coukd use date ( 1/1/2019) and format the cell to display only the month: Custom Format Type "mmm"
It will be more simple.

 

[Salamullah]

Hi PCL thanks for help