Sum formula help
 

To All,
I was wondering if anyone has a way to shorten the following formula
or is what I have the best way? I tried using Index and Match, but
could not figure out how to do it.

SUMPRODUCT(--(LEFT($B$4:$B$14,3)="200"),$F$4:$F$14)+SUMPRODUCT(--(LEFT($B$4:$B$14,3)="300"),$F$4:$F$14)+SUMPRODUCT(--(LEFT($B$4:$B$14,3)="500"),$F$4:$F$14)

Thanks
Harry

Sum formula help
 

Hi,

A bit shorter

=SUMPRODUCT(--(ISNUMBER(SEARCH({200,300,500},LEFT(B4:B14,3)))*(F 4:F14)))

Mike

"HaSt2307" wrote:

To All,
I was wondering if anyone has a way to shorten the following formula
or is what I have the best way? I tried using Index and Match, but
could not figure out how to do it.

SUMPRODUCT(--(LEFT($B$4:$B$14,3)="200"),$F$4:$F$14)+SUMPRODUCT(--(LEFT($B$4:$B$14,3)="300"),$F$4:$F$14)+SUMPRODUCT(--(LEFT($B$4:$B$14,3)="500"),$F$4:$F$14)

Thanks
Harry

Sum formula help
 

A bit shorter, but maybe not so clear

=SUMPRODUCT((1-ISERR(FIND({200,300,500},LEFT(B4:B14,3))))*(F4:F14 ))

or

=SUM((1-ISERR(FIND({200,300,500},LEFT(B4:B14,3))))*(F4:F14 ))

entered as an array formula (CTRL+SHIFT+ENTER)

Lars-Åke


On Tue, 28 Jul 2009 11:18:01 -0700, Mike H
wrote:

Hi,

A bit shorter

=SUMPRODUCT(--(ISNUMBER(SEARCH({200,300,500},LEFT(B4:B14,3)))*(F 4:F14)))

Mike

"HaSt2307" wrote:

To All,
I was wondering if anyone has a way to shorten the following formula
or is what I have the best way? I tried using Index and Match, but
could not figure out how to do it.

SUMPRODUCT(--(LEFT($B$4:$B$14,3)="200"),$F$4:$F$14)+SUMPRODUCT(--(LEFT($B$4:$B$14,3)="300"),$F$4:$F$14)+SUMPRODUCT(--(LEFT($B$4:$B$14,3)="500"),$F$4:$F$14)

Thanks
Harry

Sum formula help
 

If these are alpha-numeric codes that are actually text strings:

=SUM(SUMIF(B4:B14,{"200*","300*","500*"},F4:F14))

--
Biff
Microsoft Excel MVP


"HaSt2307" wrote in message
...
To All,
I was wondering if anyone has a way to shorten the following formula or
is what I have the best way? I tried using Index and Match, but could not
figure out how to do it.

SUMPRODUCT(--(LEFT($B$4:$B$14,3)="200"),$F$4:$F$14)+SUMPRODUCT(--(LEFT($B$4:$B$14,3)="300"),$F$4:$F$14)+SUMPRODUCT(--(LEFT($B$4:$B$14,3)="500"),$F$4:$F$14)

Thanks
Harry

Sum formula help
 

Mike,
Thanks, I knew there had to be a shorter way, but I could not seem to
find the right solution.

Harry
Mike H wrote:
Hi,

A bit shorter

=SUMPRODUCT(--(ISNUMBER(SEARCH({200,300,500},LEFT(B4:B14,3)))*(F 4:F14)))

Mike

"HaSt2307" wrote:

To All,
I was wondering if anyone has a way to shorten the following formula
or is what I have the best way? I tried using Index and Match, but
could not figure out how to do it.

SUMPRODUCT(--(LEFT($B$4:$B$14,3)="200"),$F$4:$F$14)+SUMPRODUCT(--(LEFT($B$4:$B$14,3)="300"),$F$4:$F$14)+SUMPRODUCT(--(LEFT($B$4:$B$14,3)="500"),$F$4:$F$14)

Thanks
Harry

Sum formula help
 

Lars,
Your solution works fine and I will have to study this a little more
to get fully understand it.

Harry

Lars-Åke Aspelin wrote:
A bit shorter, but maybe not so clear

=SUMPRODUCT((1-ISERR(FIND({200,300,500},LEFT(B4:B14,3))))*(F4:F14 ))

or

=SUM((1-ISERR(FIND({200,300,500},LEFT(B4:B14,3))))*(F4:F14 ))

entered as an array formula (CTRL+SHIFT+ENTER)

Lars-Åke


On Tue, 28 Jul 2009 11:18:01 -0700, Mike H
wrote:

Hi,

A bit shorter

=SUMPRODUCT(--(ISNUMBER(SEARCH({200,300,500},LEFT(B4:B14,3)))*(F 4:F14)))

Mike

"HaSt2307" wrote:

To All,
I was wondering if anyone has a way to shorten the following formula
or is what I have the best way? I tried using Index and Match, but
could not figure out how to do it.

SUMPRODUCT(--(LEFT($B$4:$B$14,3)="200"),$F$4:$F$14)+SUMPRODUCT(--(LEFT($B$4:$B$14,3)="300"),$F$4:$F$14)+SUMPRODUCT(--(LEFT($B$4:$B$14,3)="500"),$F$4:$F$14)

Thanks
Harry

Sum formula help
 

Biff,
Yes they are in text strings i.e. 300 - Misc Income and your solution
also works. I will have to also file this one away for later use.

Harry


T. Valko wrote:
If these are alpha-numeric codes that are actually text strings:

=SUM(SUMIF(B4:B14,{"200*","300*","500*"},F4:F14))

Sum formula help
 

Your welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"HaSt2307" wrote in message
...
Biff,
Yes they are in text strings i.e. 300 - Misc Income and your solution
also works. I will have to also file this one away for later use.

Harry


T. Valko wrote:
If these are alpha-numeric codes that are actually text strings:

=SUM(SUMIF(B4:B14,{"200*","300*","500*"},F4:F14))