sum multiple Adjacent columns based on TRUE or FALSE

[Dares2]

hi everyone,

I have a sheet (which updates the forecast sheet with actuals and therefore equals TRUE if its an actual column).
I am wanting to sum multiple columns based on the top of the column (row 11) being TRUE but summing all adjacent columns up to one column to the left of the last TRUE column and then add the last month of forecast. This then sums all the columns that are True up to the previous month and adds the current month of forecast column. for eg below would sum only July and add a forecast column for Aug month from an identical sheet.

[TABLE="width: 539"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 5"] [/TD]
[/TR]
[TR]
[TD="colspan: 2"]Profit & Loss Forecast[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]July 2018 To June 2019[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Month No.[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD]Account Name[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[/TR]
[TR]
[TD]Month is actual[/TD]
[TD][/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]TRUE[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]4-0000
[/TD]
[TD]Income[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] 4-1000[/TD]
[TD] Interest Received[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] 4-1003[/TD]
[TD] Interest received Term Deposit[/TD]
[TD]$2,467.73[/TD]
[TD]$1,212.31[/TD]
[TD]$2,000.00[/TD]
[/TR]
[TR]
[TD] 4-1004[/TD]
[TD] Interest received Cash at Bank[/TD]
[TD]$4,195.94[/TD]
[TD] [/TD]
[TD]$39.46[/TD]
[/TR]
[TR]
[TD] 4-2000[/TD]
[TD] Loan fees[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] 4-2001[/TD]
[TD] Application fees [/TD]
[TD] [/TD]
[TD]$1,812.50[/TD]
[TD]$3,964.02[/TD]
[/TR]
</tbody>[/TABLE]


Can anyone help with this as not sure it is Sumproduct etc. I thought the first bit was [TABLE="width: 89"]
<tbody>[TR]
[TD]=SUM(IF(D11:O11="TRUE",D14:O14,0))[/TD]
[/TR]
</tbody>[/TABLE]
but this didnt work.

thanks
Darren

 

[Aladin Akyurek]

Looks like:

=IF(COUNTIFS(D11:O11,TRUE)>1,SUM(D14:INDEX(D14:O14,COUNTIFS(D11:O11,TRUE)-1)),0)

 

[Dares2]

Thanks Aladin, I seem to get a circular reference. =IF(COUNTIFS($D11:$O11,TRUE)>1,SUM(D14:INDEX($D14:$O14,COUNTIFS($D11:$O11,TRUE)-1)),0). Am I meant to reference the look up sheet as well ie after the index reference? ie sum D14 within a difference sheet index D14:014?

 

[Dares2]

I take that back and it does work thankyou Aladin for seeing to my excel dilemma.

 

[Aladin Akyurek]

I take that back and it does work thankyou Aladin for seeing to my excel dilemma.

You are welcome. Glad to see it's sorted out.