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.

Profit & Loss Forecast
July 2018 To June 2019
Month No.		1	2	3
Month	Account Name	Jul	Aug	Sep
Month is actual		TRUE	TRUE	FALSE
4-0000	Income
4-1000	Interest Received
4-1003	Interest received Term Deposit	$2,467.73	$1,212.31	$2,000.00
4-1004	Interest received Cash at Bank	$4,195.94		$39.46
4-2000	Loan fees
4-2001	Application fees		$1,812.50	$3,964.02

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>

Can anyone help with this as not sure it is Sumproduct etc. I thought the first bit was

=SUM(IF(D11:O11="TRUE",D14:O14,0))

<tbody>
</tbody>

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.