SUM OFFSET but subtracting subtotals

[erniepoe]

Hi all,

I am using the following standard sum offset to do a year to date formula, the A3 and V8:AJ8 are just matching the names of the month from a dropdown list and lookup.

The formula works fine, but my rows across are:

JAN FEB MAR Q1 APR MAY JUNE Q2 JULY AUG SEP Q3 OCT NOV DEC Q4

so the sum formula is also adding in the Q1 and Q2 (since we are currently doing August).

Is there any way to update this formula to NOT include the totals in Q1, Q2, Q3, and Q4, regardless of what month is showing and only include the monthly totals?

Thanks so much!



=SUM(OFFSET(V13,0,0,1,MATCH($A$3,$V$8:$AJ$8,0)))

 

[KUYJS]

As per your table and formula it seems, Dec is in cell AJ13 so Q4 will never get added, if i am correct here below formula should work:

=SUM(OFFSET(V13,0,0,1,MATCH($A$3,$V$8:$AJ$8,0)))-if(MATCH($A$3,$V$8:$AJ$8,0)>=12,AG13,if(MATCH($A$3,$V$8:$AJ$8,0)>=8,AC13,IF(MATCH($A$3,$V$8:$AJ$8,0)>=4,Y13,0)))

 

[erniepoe]

As per your table and formula it seems, Dec is in cell AJ13 so Q4 will never get added, if i am correct here below formula should work:

=SUM(OFFSET(V13,0,0,1,MATCH($A$3,$V$8:$AJ$8,0)))-if(MATCH($A$3,$V$8:$AJ$8,0)>=12,AG13,if(MATCH($A$3,$V$8:$AJ$8,0)>=8,AC13,IF(MATCH($A$3,$V$8:$AJ$8,0)>=4,Y13,0)))

Thank you KUYJS, that didn't completely work but I think you are on the right track, I will try to play with it a bit further, but im sure this template will be extremely helpful as I tweak it.

As per the data below, the correct YTD answer thru August should be $1.887M, whereas this formula returns $2.55M. Thanks though, cheers!





Jan-18 Feb-18 Mar-18 Q1:2018 Apr-18 May-18 Jun-18 Q2:2018 Jul-18 Aug-18 Sep-18 Q3:2018 Oct-18 Nov-18 Dec-18 Q4:2018 2018
Plan Plan Plan Plan Plan Plan Plan YTD



213,000 217,167 232,586 662,752 241,666 241,666 241,666 724,999 241,666 258,333 267,258 767,257 267,258 267,258 267,258 801,774 2,550,502.67

 

[FlameRetired]

With the data in the range you describe extended to column AK try this.

=SUMIF($V$8:INDEX($V$8:$AK$8,,MATCH($A$3,$V$8:$AK$8,0)),"<>Q*",$V$9:INDEX($V$9:$AK$9,,MATCH($A$3,$V$8:$AK$8,0)))

 

[erniepoe]

hmm, that didnt work either. All of the months are in row 8, not 9. I changed the 9s to 8s but that was unsuccessful as well.

But thanks for the effort, I sincerely appreciate it!

 

[FlameRetired]

This is how I understood the layout. Formula is in V3.


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

V​

[/td][td="bgcolor: #DCE6F1"]

W​

[/td][td="bgcolor: #DCE6F1"]

X​

[/td][td="bgcolor: #DCE6F1"]

Y​

[/td][td="bgcolor: #DCE6F1"]

Z​

[/td][td="bgcolor: #DCE6F1"]

AA​

[/td][td="bgcolor: #DCE6F1"]

AB​

[/td][td="bgcolor: #DCE6F1"]

AC​

[/td][td="bgcolor: #DCE6F1"]

AD​

[/td][td="bgcolor: #DCE6F1"]

AE​

[/td][td="bgcolor: #DCE6F1"]

AF​

[/td][td="bgcolor: #DCE6F1"]

AG​

[/td][td="bgcolor: #DCE6F1"]

AH​

[/td][td="bgcolor: #DCE6F1"]

AI​

[/td][td="bgcolor: #DCE6F1"]

AJ​

[/td][td="bgcolor: #DCE6F1"]

AK​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

AUG​

[/td][td]

$22,956​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td]

JAN​

[/td][td]

FEB​

[/td][td]

MAR​

[/td][td]

Q1​

[/td][td]

APR​

[/td][td]

MAY​

[/td][td]

JUNE​

[/td][td]

Q2​

[/td][td]

JULY​

[/td][td]

AUG​

[/td][td]

SEP​

[/td][td]

Q3​

[/td][td]

OCT​

[/td][td]

NOV​

[/td][td]

DEC​

[/td][td]

Q4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td]

$4,420​

[/td][td]

$4,141​

[/td][td]

$1,114​

[/td][td="bgcolor:#FFFF00"]

$9,675​

[/td][td]

$2,003​

[/td][td]

$4,578​

[/td][td]

$1,662​

[/td][td="bgcolor:#FFFF00"]

$8,243​

[/td][td]

$2,579​

[/td][td]

$2,459​

[/td][td]

$4,047​

[/td][td="bgcolor:#FFFF00"]

$9,085​

[/td][td]

$4,818​

[/td][td]

$3,624​

[/td][td]

$4,103​

[/td][td="bgcolor:#FFFF00"]

$12,545​

[/td][/tr]
[/table]