[N Drinkwater]

Hi,

I have exported a table of monthly account balances in the following layout:
a/c, descr, bal b/f, mvt mth 1, mvt mth 2, etc....

and to summarise the balance at any given point in the year I need to add
the columns together. I have used various SUMIF and SUM(IF array formulas to
get what I need bases on matching text from the description column. However
I am struggling with my loans as the descriptions are inconsistent, i.e. CT
Loan and Loan to ABC, etc.

A SUMIF will let me use 'wild cards' to match *Loan* but will only total the
first column (so fine for an opening balance) and an array such as SUM(IF
doesn't seem to allow wild cards to match all loans - though when used with
exact text add the relevant number of columns I enter.

Am I trying the impossible - it can't be that hard can it?

Thanks in advance of your help

[RagDyeR]

Say your "descr" is in B2 to B20,
and the monthly values are in D2 to O20,
then try this:

=SUMPRODUCT((ISNUMBER(SEARCH("loan",B2:B20)))*D2:O 20)
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"N Drinkwater" <N wrote in message
...
Hi,

I have exported a table of monthly account balances in the following layout:
a/c, descr, bal b/f, mvt mth 1, mvt mth 2, etc....

and to summarise the balance at any given point in the year I need to add
the columns together. I have used various SUMIF and SUM(IF array formulas
to
get what I need bases on matching text from the description column. However
I am struggling with my loans as the descriptions are inconsistent, i.e. CT
Loan and Loan to ABC, etc.

A SUMIF will let me use 'wild cards' to match *Loan* but will only total the
first column (so fine for an opening balance) and an array such as SUM(IF
doesn't seem to allow wild cards to match all loans - though when used with
exact text add the relevant number of columns I enter.

Am I trying the impossible - it can't be that hard can it?

Thanks in advance of your help

[N Drinkwater[_2_]]

Genius. This works a treat, thanks.

"RagDyeR" wrote:

Say your "descr" is in B2 to B20,
and the monthly values are in D2 to O20,
then try this:

=SUMPRODUCT((ISNUMBER(SEARCH("loan",B2:B20)))*D2:O 20)
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"N Drinkwater" <N wrote in message
...
Hi,

I have exported a table of monthly account balances in the following layout:
a/c, descr, bal b/f, mvt mth 1, mvt mth 2, etc....

and to summarise the balance at any given point in the year I need to add
the columns together. I have used various SUMIF and SUM(IF array formulas
to
get what I need bases on matching text from the description column. However
I am struggling with my loans as the descriptions are inconsistent, i.e. CT
Loan and Loan to ABC, etc.

A SUMIF will let me use 'wild cards' to match *Loan* but will only total the
first column (so fine for an opening balance) and an array such as SUM(IF
doesn't seem to allow wild cards to match all loans - though when used with
exact text add the relevant number of columns I enter.

Am I trying the impossible - it can't be that hard can it?

Thanks in advance of your help

[RagDyeR]

You're welcome, and thanks for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"N Drinkwater" wrote in message
...
Genius. This works a treat, thanks.

"RagDyeR" wrote:

Say your "descr" is in B2 to B20,
and the monthly values are in D2 to O20,
then try this:

=SUMPRODUCT((ISNUMBER(SEARCH("loan",B2:B20)))*D2:O 20)
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"N Drinkwater" <N wrote in message
...
Hi,

I have exported a table of monthly account balances in the following
layout:
a/c, descr, bal b/f, mvt mth 1, mvt mth 2, etc....

and to summarise the balance at any given point in the year I need to add
the columns together. I have used various SUMIF and SUM(IF array formulas
to
get what I need bases on matching text from the description column.
However
I am struggling with my loans as the descriptions are inconsistent, i.e.
CT
Loan and Loan to ABC, etc.

A SUMIF will let me use 'wild cards' to match *Loan* but will only total
the
first column (so fine for an opening balance) and an array such as SUM(IF
doesn't seem to allow wild cards to match all loans - though when used
with
exact text add the relevant number of columns I enter.

Am I trying the impossible - it can't be that hard can it?

Thanks in advance of your help