ytd values based on 3 criteria

[sureshsonti]

Hi,
I am trying to compute YTD values based on 3 criteria (Account ID, Month, and Year)

I have a tab with units sold data, one tab with ledger_data which has all the sales with account id.

In the sales tab I want to compute YTD values (Units and Dollar values) based on Account ID, Year and Month)

If I change the Year and Month the highlighted red cells should reflect the year and the months. Example Year 2017 and if I enter Jul, the Units and Dollar amounts should sum from JAN to JULY.

I have attached a sample sheet.

Thanks for all your help.

 

[bosco_yip]

Perhaps..............

1] In AO5, copied down :

=IF($D5="","",SUMIF($D$5:INDEX($D$5:$AK$5,MATCH($AO$2,$E$3:$AL$3,0)),"UNITS",$E5))

2] Click dropdown list in cell AO2 to select month of the year, and check the values changing

Regards
Bosco

 

[sureshsonti]

Thanks Mr. Bosco. This is perfect. Thanks for your help

 

[sureshsonti]

Mr. Bosco,
I have one more change to the format of the spreadsheet. If I change the direction of the month on the top that is from JAN FEB MAR to MAR FEB JAN, the formula is calculating only the MAR values. Can you help me with this issue.

Thanks

 

[sureshsonti]

I forgot to mention that I plan to use this as a 3 month report from the current month. For example if this is MAR, the report would start from MAR FEB JAN.

If the month is OCT, the report will start from OCT SEP AUG.

Thanks.

 

[bosco_yip]

Mr. Bosco,
I have one more change to the format of the spreadsheet. If I change the direction of the month on the top that is from JAN FEB MAR to MAR FEB JAN, the formula is calculating only the MAR values. Can you help me with this issue.

Thanks

In AO5, copied down :

=IF($D5="","",SUMIF(OFFSET($J$5,,,,MATCH($AO$2,$E$3:$K$3,0)-8),"UNITS",OFFSET($L5,,MATCH($AO$2,$E$3:$K$3,0)-8)))

Regards
Bosco

 

[sureshsonti]

In AO5, copied down :

=IF($D5="","",SUMIF(OFFSET($J$5,,,,MATCH($AO$2,$E$3:$K$3,0)-8),"UNITS",OFFSET($L5,,MATCH($AO$2,$E$3:$K$3,0)-8)))

Regards
Bosco

Thanks Mr. Bosco. It works perfect. Thanks again