| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
