CODEWITHSUNDEEP
if-statementgoogle-sheetsfiltersumarray-formulas
vinny
vinny

Reputation: 13

SUM of cells based on the value of cells on the right and above

Example of my working sheet

Y3 (Total forecast) = Sum of all actuals for the months that have actuals + Sum of all forecast for the remaining months where there are no actuals yet.

Currently, I am using this formula: Y3 

=SUMIF(A2:H2, Z2, A3:H3)+SUMIF(I2:X2, Y2, I3:X3)

It works. But I have to manually update the formula every month when actual costs are in. So I want to set up a formula that dynamically changes the sum when the cell refers to "Actual" is not empty.

I've tried SUM with OFFSET, INDIRECT and ADDRESS. Nothing works for me so far.

Upvotes: 1

Views: 63

Answers (2)

user11982798
user11982798

Reputation: 1908

Use this formula:

=IFERROR(SUM(FILTER(A3:X3,{B3:X3,""}<>"",A2:X2=Y2)),0)

and

=IFERROR(SUM(FILTER(A3:X3,A3:X3<>"",A2:X2=Z2)),0)

Update:

=arrayformula(sum(if(A2:X2="Actual",A3:X3,0)+
                  if(A2:X2="Forecast",if({B3:X3,""}="",A3:X3,0),0)))

This can be change with sumif, but I have not tested it

Another 1:

=arrayformula(sum(A3:X3-if(A2:X2="Forecast",if({B3:X3,""}<>"",A3:X3,0),0)))

Another 2:

=arrayformula(sum(if (A2:X2="forecast",if({B3:X3,0}="",A3:X3,{B3:X3,0}),0)))

Upvotes: 0

player0
player0

Reputation: 1

use in Y3 cell:

=ARRAYFORMULA(SUM(FILTER(A3:X3, A2:X2="actual"),
 INDIRECT(ADDRESS(3, MIN(IF(A3:X3="", COLUMN(A:X)-1, )))&":X3")))

0

Upvotes: 1

Related Questions