Excel - Sum QTD values based on selected month

1
votes

Desired end state: The QTD field sums the quarterly values to date so as an example for October it would return just the October value (1st month in Q4) but when November is selected as the driver the QTD field would sum Oct + Nov values (ignoring the value for Dec).

I have a spreadsheet with drivers and the current date will be updated daily. This date drives the "Current Month" field from which I want the QTD values to recalculate.

See example linked below. Expected results - Cat 1 QTD for Nov would be 3,387,280; Cat13 QTD for April would be 1,121,021 and so on

enter image description here

Thanks in advance!

2
excelexcel-formula
Can you please edit your question to include sample data? many people will not download files from unknown sources and your question will be useless once the link goes dead – cybernetic.nomad

2 Answers

2
votes

In O6:

=SUMIFS(C6:N6,$C$4:$N$4,"<="&MONTH("1 "&A2),$C$4:$N$4,">="&MATCH(INDEX($1:$1,MATCH(MONTH("1 "&A2),$4:$4,0)),$C1:$N1,0))

enter image description here

2
votes

Alternative approach using SUMPRODUCT:

=SUMPRODUCT(C6:N6,--(("Q"&ROUNDUP(MONTH(1&$A$2)/3,0))=$C$1:$N$1),--($C$4:$N$4<=MONTH(1&$A$2)))

Result:

enter image description here