Sumif

[Guest]

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,

 

[RagDyeR]

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!
=====================================================


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,

 

[RagDyeR]

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!
=====================================================

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!
=====================================================


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,

 

[Roger Govier]

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, Data>Text to Columns>Delimited>Select 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.

 

[Guest]

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

 

[RagDyeR]

And we appreciate your feed-back.