Summing partial strings.
 

For some time now I have been downloading bank statements. I have
columns for Transaction Date, Transaction Details, Amount, Balance, and
one transaction per row and one worksheet per month.

Transaction Details is a variable string, but there are some common
elements, e.g Cheque No 0001, Cheque No 0002.

What I would like to be able to do on each months worksheet is to
automatically sum all of the outgoings to Cheques. So I need a
function/formulae that will look in the Transaction Details column for
'Cheque', i.e. part of the string, and sum all corresponding
Amounts in the adjacent column and put the answer in a 'Total Cheques'
row that I will insert further down the sheet.

I suspect I could use the 'SUMIF' function, but don't know how to do
the partial string bit.

What's the best/easiest way of doing this?

Does anyone have a ready made formulae?

I would then want to modify this for other partial strings, e.g. Cash
Machine, on the same worksheet.

TIA.

Assume your 'Cheque No 0001..." values are in the range A1:A100 and the
corresponding Amount's are in the range B1:B100:

=SUMPRODUCT(--ISNUMBER(SEARCH("Cheque",A1:A100)),B1:B100)

Just change the row/column ranges to match your data.


--
Regards,
Dave


" wrote:

For some time now I have been downloading bank statements. I have
columns for Transaction Date, Transaction Details, Amount, Balance, and
one transaction per row and one worksheet per month.

Transaction Details is a variable string, but there are some common
elements, e.g Cheque No 0001, Cheque No 0002.

What I would like to be able to do on each months worksheet is to
automatically sum all of the outgoings to Cheques. So I need a
function/formulae that will look in the Transaction Details column for
'Cheque', i.e. part of the string, and sum all corresponding
Amounts in the adjacent column and put the answer in a 'Total Cheques'
row that I will insert further down the sheet.

I suspect I could use the 'SUMIF' function, but don't know how to do
the partial string bit.

What's the best/easiest way of doing this?

Does anyone have a ready made formulae?

I would then want to modify this for other partial strings, e.g. Cash
Machine, on the same worksheet.

TIA.

With the descriptions in column A and the amounts in column B, use the array
formula (entered with Ctrl+Shift+Enter)

=SUMPRODUCT(--ISNUMBER(SEARCH("cheque",A1:A4)),B1:B4)


" wrote:

For some time now I have been downloading bank statements. I have
columns for Transaction Date, Transaction Details, Amount, Balance, and
one transaction per row and one worksheet per month.

Transaction Details is a variable string, but there are some common
elements, e.g Cheque No 0001, Cheque No 0002.

What I would like to be able to do on each months worksheet is to
automatically sum all of the outgoings to Cheques. So I need a
function/formulae that will look in the Transaction Details column for
'Cheque', i.e. part of the string, and sum all corresponding
Amounts in the adjacent column and put the answer in a 'Total Cheques'
row that I will insert further down the sheet.

I suspect I could use the 'SUMIF' function, but don't know how to do
the partial string bit.

What's the best/easiest way of doing this?

Does anyone have a ready made formulae?

I would then want to modify this for other partial strings, e.g. Cash
Machine, on the same worksheet.

TIA.

Thanks David & Duke - it's easy when you know how.

Hi,

You may try the following array formula (Ctrl+Shift+Enter)

=SUM(IF(ISNUMBER(FIND(B10,$B$5:$B$8)),$C$5:$C$8))

where cell B10 contains "Cheque". You may change this to what you want
later.
$B$5:$B$8 contains "Transaction details" and $C$5:$C$8 contains "Amounts"

Regards,

Ashish Mathur

" wrote:

For some time now I have been downloading bank statements. I have
columns for Transaction Date, Transaction Details, Amount, Balance, and
one transaction per row and one worksheet per month.

Transaction Details is a variable string, but there are some common
elements, e.g Cheque No 0001, Cheque No 0002.

What I would like to be able to do on each months worksheet is to
automatically sum all of the outgoings to Cheques. So I need a
function/formulae that will look in the Transaction Details column for
'Cheque', i.e. part of the string, and sum all corresponding
Amounts in the adjacent column and put the answer in a 'Total Cheques'
row that I will insert further down the sheet.

I suspect I could use the 'SUMIF' function, but don't know how to do
the partial string bit.

What's the best/easiest way of doing this?

Does anyone have a ready made formulae?

I would then want to modify this for other partial strings, e.g. Cash
Machine, on the same worksheet.

TIA.