Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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". |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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" |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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" |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Showing the cell to the Left when certain criteria are met | Excel Worksheet Functions | |||
sumif or sumproduct with a left statement | Excel Discussion (Misc queries) | |||
SUMPRODUCT AND LEFT | Excel Worksheet Functions | |||
Left and Sumproduct | Excel Discussion (Misc queries) | |||
Varying left criteria based on 1st Letter....If Function? | Excel Discussion (Misc queries) |