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
|