OFFSET MATCH SUM - summing cash flows

[jdmc45]

Hi,

I have some columns and rows of cash flows, so from columns A to F I have Cashflow 1, Cashflow2, ... Cashflow 6, and down to row 100 say. I would like to sum these cash flows, say in column Z. The problem is in later datasets the number of columns of cash flows could change, it could only be columns A to C with Cashflow 1, Cashflow 2, Cashflow 3 or it could be columns A to K with 11 cashflows, etc. How would I use an offset match and sum to sum the whole row of cash flows given that the header has the word "Cashflow" in it?

Thanks

 

[Aladin Akyurek]

Posting a tiny sample would be helpful.

 

[jdmc45]

Posting a tiny sample would be helpful.

A     B      C        D
$1    $4    $5       $5

want to sum this row, and all subsequent rows, based on the number of columns. The number of columns could increase or decrease, but all will be labelled as CF, column A CF1, column B CF2 etc.

How do I sum each row based on the number of columns, which is variable?

I came up with something like

=SUM(OFFSET(A1,ROWS($A$2:A2),0,1,MATCH("CF",LEFT(1:1,2),0)))

but LEFT doesn't work on arrays

 

[Aladin Akyurek]

I don't see any CF in the sample you posted.

 

[jdmc45]

I don't see any CF in the sample you posted.

The first code segment are the cash flows. with the dollar signs in front of them.

 

[Aladin Akyurek]

The first code segment are the cash flows. with the dollar signs in front of them.

I'm sorry I don't understand: You also posted a formula where you match "CF" against 1:1. The tiny sample you posted does not contain anything displaying "CF". Your data and its lay-out probably appears obvious to you, but, at least, not to me. Hence the request for a tiny sample.

 

[jdmc45]

I'm sorry I don't understand: You also posted a formula where you match "CF" against 1:1. The tiny sample you posted does not contain anything displaying "CF". Your data and its lay-out probably appears obvious to you, but, at least, not to me. Hence the request for a tiny sample.

A         B            C                D                E                F
ID       Code       CF1            CF2              CF3             CF4
1234     AU         -20          10                 15                 20
1345     BK         -547        24                42                36

Hope that helps

 

[Aladin Akyurek]

Great. Could you also specify the desired result per ID if it's not just sum CF values as in:

In Z2 enter:

=SUMIF($C$1:$Y$1,"CF*",C2:Y2)

 

[anandvarma]

if you still wants offset function to work

try this
in cell Z2

=SUM(OFFSET(C2,,,,COUNT(C2:Y2)))
Data start from c2 to y2

 

[jdmc45]

if you still wants offset function to work

try this
in cell Z2

=SUM(OFFSET(C2,,,,COUNT(C2:Y2)))
Data start from c2 to y2

Thank you for your solution. Also, thankyou Aladin as yours works too. I think I understand your formula Aladin, your formula looks for CF in any of the cells in the row specified and if CF does appear (by using the *) then it will sum the cash flows.

Anandvarma I have no idea how your formula works. Do you mind explaining it to me? Cheers!