Complex sum required
 

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

Complex sum required
 

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

Complex sum required
 

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

Complex sum required
 

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