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.


"WildWill" wrote in message
...
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".