Forum Discussion

emmcee's avatar
emmcee
Copper Contributor
Feb 11, 2020

Working with dates

Hi all I have a spreadsheet which I add sales figures to each month. On this spread sheet I have a tab with all my budget figures for each month and a tab with product analysis. On the products tab ...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 11, 2020

emmcee 

First you may calculate current month for YTD sales list as MONTH(MAX(Sales!$A:$A))

It returns 1 for Jan, 2 for Feb, etc.

Range of budget figures from the beginning of the year (Jan) till current month could be calculated as

$B$2:INDEX($B$2:$B$13, <month>). For example for Feb that will be $B$2:INDEX($B$2:$B$13,2) which is equivalent of $B$2:$B$3. Same for other months. All what you need now is to sum that range.

Combine all together, for Home budget

=SUM(Budget!$B$2:INDEX(Budget!$B$2:$B$13,MONTH(MAX(Sales!$A:$A))))

Please see in attached file.

emmcee's avatar
emmcee
Copper Contributor
Feb 12, 2020

SergeiBaklan 

Hi again Sergei

Is there a way to make this work if the budget data is not in a convenient column?

I have 18 different product groups and the budget is currently arranged Month January, Month February etc. so I'm guessing the index($b$2:$b$13 in your example won't be the right terminology. I need to be able to pick up January line 1, February  line 13, March line 25 etc. Do you see what I mean?

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 14, 2020

    emmcee 

    If by product when it could be SUMIFS() of budgets for all month less or equal than current and for given product.

    To be more concrete it's desirable to have the sample with more or less actual data structure.

    • emmcee's avatar
      emmcee
      Copper Contributor
      Feb 18, 2020

      SergeiBaklan 

      Hi Sergei

      I understand. Thank you for all your help it's been invaluable and I'm pleased to say that it's working perfectly.

      Many thanks

Resources