SUMPRODUCT with LEFT() criteria?
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". |
SUMPRODUCT with LEFT() criteria?
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" wrote: 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" |
SUMPRODUCT with LEFT() criteria?
Hi Max
I picked that up as I clicked on "submit" :-), but too late. Thanks for the quick response! "Max" wrote: 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" wrote: 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" |
SUMPRODUCT with LEFT() criteria?
No prob, you're welcome.
-- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:362 Subscribers:64 xdemechanik --- "WildWill" wrote in message ... Hi Max I picked that up as I clicked on "submit" :-), but too late. Thanks for the quick response! |
SUMPRODUCT with LEFT() criteria?
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. Max wrote: 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" wrote: 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" -- Dave Peterson |
SUMPRODUCT with LEFT() criteria?
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". |
All times are GMT +1. The time now is 06:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com