Sumif
 

Hi,
I have all my General Ledger transactions imported in an Excel spreadsheet
from my accounting system like this:
Code Amount
65-55-10-654 1,200
75-55-11-856 1,000
85-57-10-864 100
65-58-13-024 50

I need a formula in one line that can sum up the amount if the range of
characters between the 4th and the 8th string is between 55-10 and 55-14,
which in this example is 2,200.

Thanks in advance,
--
tarig

Sumif
 

Try this:
=SUMPRODUCT((MID(A1:A15,4,2)="55")*(MID(A1:A15,7,2 )={"10","11","12","13","14"})*B1:B15)

--

HTH,

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


"Tarig" wrote in message
...
Hi,
I have all my General Ledger transactions imported in an Excel spreadsheet
from my accounting system like this:
Code Amount
65-55-10-654 1,200
75-55-11-856 1,000
85-57-10-864 100
65-58-13-024 50

I need a formula in one line that can sum up the amount if the range of
characters between the 4th and the 8th string is between 55-10 and 55-14,
which in this example is 2,200.

Thanks in advance,
--
tarig

Sumif
 

If the range of your second set of characters might sometimes be larger then
the 5 numbers in your original example, say 10 to 25, you could use this
instead:

=SUMPRODUCT((MID(A1:A15,4,2)="55")*(MID(A1:A15,7,2 )="10")*(MID(A1:A15,7,2)<="25")*B1:B15)

--

HTH,

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

"RagDyeR" wrote in message
...
Try this:
=SUMPRODUCT((MID(A1:A15,4,2)="55")*(MID(A1:A15,7,2 )={"10","11","12","13","14"})*B1:B15)

--

HTH,

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


"Tarig" wrote in message
...
Hi,
I have all my General Ledger transactions imported in an Excel spreadsheet
from my accounting system like this:
Code Amount
65-55-10-654 1,200
75-55-11-856 1,000
85-57-10-864 100
65-58-13-024 50

I need a formula in one line that can sum up the amount if the range of
characters between the 4th and the 8th string is between 55-10 and 55-14,
which in this example is 2,200.

Thanks in advance,
--
tarig

Sumif
 

Hi Tarig

In addition to RagDyer's excellent formulae, you could make life a
little easier if you inserted 3 columns between Code and Amount after
importing. Format these 4 columns as Text.
Mark column A, DataText to ColumnsDelimitedSelect Other delimiter and
enter a "-" in the box (without quotes)Finish.
Your code number will be split into 4 columns.
The reason for choosing a Text format is to preserve the leading 0 on
some of the numbers that would be created.

You could now apply an Autofilter if required to look at any subset, and
your formulae would become easier
e.g.
=SUMPRODUCT((B1:B15)="55")*(C1:C15={"10","11","12" ,"13","14"})*B1:B15)

Just a thought.

--
Regards

Roger Govier


"Tarig" wrote in message
...
Hi,
I have all my General Ledger transactions imported in an Excel
spreadsheet
from my accounting system like this:
Code Amount
65-55-10-654 1,200
75-55-11-856 1,000
85-57-10-864 100
65-58-13-024 50

I need a formula in one line that can sum up the amount if the range
of
characters between the 4th and the 8th string is between 55-10 and
55-14,
which in this example is 2,200.

Thanks in advance,
--
tarig

Sumif
 

Thank you guys, your suggestions really made my life easier!
--
tarig


"Roger Govier" wrote:

Hi Tarig

In addition to RagDyer's excellent formulae, you could make life a
little easier if you inserted 3 columns between Code and Amount after
importing. Format these 4 columns as Text.
Mark column A, DataText to ColumnsDelimitedSelect Other delimiter and
enter a "-" in the box (without quotes)Finish.
Your code number will be split into 4 columns.
The reason for choosing a Text format is to preserve the leading 0 on
some of the numbers that would be created.

You could now apply an Autofilter if required to look at any subset, and
your formulae would become easier
e.g.
=SUMPRODUCT((B1:B15)="55")*(C1:C15={"10","11","12" ,"13","14"})*B1:B15)

Just a thought.

--
Regards

Roger Govier


"Tarig" wrote in message
...
Hi,
I have all my General Ledger transactions imported in an Excel
spreadsheet
from my accounting system like this:
Code Amount
65-55-10-654 1,200
75-55-11-856 1,000
85-57-10-864 100
65-58-13-024 50

I need a formula in one line that can sum up the amount if the range
of
characters between the 4th and the 8th string is between 55-10 and
55-14,
which in this example is 2,200.

Thanks in advance,
--
tarig

Sumif
 

And we appreciate your feed-back.
--

Regards,

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

"Tarig" wrote in message
...
Thank you guys, your suggestions really made my life easier!
--
tarig


"Roger Govier" wrote:

Hi Tarig

In addition to RagDyer's excellent formulae, you could make life a
little easier if you inserted 3 columns between Code and Amount after
importing. Format these 4 columns as Text.
Mark column A, DataText to ColumnsDelimitedSelect Other delimiter and
enter a "-" in the box (without quotes)Finish.
Your code number will be split into 4 columns.
The reason for choosing a Text format is to preserve the leading 0 on
some of the numbers that would be created.

You could now apply an Autofilter if required to look at any subset, and
your formulae would become easier
e.g.
=SUMPRODUCT((B1:B15)="55")*(C1:C15={"10","11","12" ,"13","14"})*B1:B15)

Just a thought.

--
Regards

Roger Govier


"Tarig" wrote in message
...
Hi,
I have all my General Ledger transactions imported in an Excel
spreadsheet
from my accounting system like this:
Code Amount
65-55-10-654 1,200
75-55-11-856 1,000
85-57-10-864 100
65-58-13-024 50

I need a formula in one line that can sum up the amount if the range
of
characters between the 4th and the 8th string is between 55-10 and
55-14,
which in this example is 2,200.

Thanks in advance,
--
tarig