SUMPRODUCT with LEFT() criteria?

[WildWill]

Hi

I have this formula as a basis:
SUMPRODUCT(--(N3:N46="Bob"),--(J3:J46="Apple")) I need to modify the above
formula so that it will sum all instances where N3:N46 = "Bob" and where
J3:J46 contains text that starts (from the left) with the 4 characters
"Feline".

 

[Max]

Since "Feline" = 6 characters (not 4)
Try: =SUMPRODUCT(--(N3:N46="Bob"),--(LEFT(J3:J46,6)="Feline"))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik

 

[WildWill]

Hi Max

I picked that up as I clicked on "submit" , but too late. Thanks for the
quick response!

 

[Max]

No prob, you're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:362 Subscribers:64
xdemechanik

 

[Dave Peterson]

Just to add to Max's solution...

You may want to write the formula like:

=SUMPRODUCT(--(N3:N46="Bob"),--(LEFT(J3:J46,len("feline"))="Feline"))

It may help you notice the things you have to adjust if you change the formula.

 

[Shane Devenshire]

Hi,

Not to take anything away from Max or Dave's suggestions but using range
names and cell address rather than hard coding text into formulas allow much
greater flexibility, for example, your formula could become:

=SUMPRODUCT(--(Name=A1),--(LEFT(Type,6)=A2))

This has the advantage that you can enter a different name is cell A1 and
voila' a new result. If you want to use the A2 reference more effectively,
you can use Dave's suggestion:

=SUMPRODUCT(--(Name=A1),--(LEFT(Type,LEN(Type)=A2))

One advantage to range names is that if the ranges are on other sheets you
don't need to include the sheet reference. Also name are more informative
than cell addresses in many cases.