vlookup and multiple criteria
in [Functions]
|
From: aprendiz on 15 Feb 2010 10:25 I have an spreadsheet with columns like: col A col D col E colF B230 8 1 0 F001 -5 0 10 F002 3 20 0 F010 5 -29 3 F014 40 3 7 F020 -25 -6 -100 F113 0 0 0 H002 34 -50 23 in ascendent order. I want to be able to Sum col D to colF if colA is between F001 and F020 except F010. I am just starting with this and I really don't know how to mix and match all the formulas. any help will be much appreaciated!!
From: Teethless mama on 15 Feb 2010 11:44 =SUMPRODUCT((ISNUMBER(MATCH(A1:A8,{"F001","F020"},)))*D1:F8) "aprendiz" wrote: > I have an spreadsheet with columns like: > > col A col D col E colF > B230 8 1 0 > F001 -5 0 10 > F002 3 20 0 > F010 5 -29 3 > F014 40 3 7 > F020 -25 -6 -100 > F113 0 0 0 > H002 34 -50 23 > in ascendent order. > I want to be able to Sum col D to colF if colA is between F001 and F020 > except F010. > I am just starting with this and I really don't know how to mix and match > all the formulas. any help will be much appreaciated!!
From: Teethless mama on 15 Feb 2010 11:52 Try this one: =SUMPRODUCT((LEFT(A1:A8)="F")*(RIGHT(A1:A8,3)+0>=1)*(RIGHT(A1:A8,3)+0<=20)*(RIGHT(A1:A8,3)+0<>10)*D1:F8) "Teethless mama" wrote: > =SUMPRODUCT((ISNUMBER(MATCH(A1:A8,{"F001","F020"},)))*D1:F8) > > > "aprendiz" wrote: > > > I have an spreadsheet with columns like: > > > > col A col D col E colF > > B230 8 1 0 > > F001 -5 0 10 > > F002 3 20 0 > > F010 5 -29 3 > > F014 40 3 7 > > F020 -25 -6 -100 > > F113 0 0 0 > > H002 34 -50 23 > > in ascendent order. > > I want to be able to Sum col D to colF if colA is between F001 and F020 > > except F010. > > I am just starting with this and I really don't know how to mix and match > > all the formulas. any help will be much appreaciated!!
From: Glenn on 15 Feb 2010 11:55 Looks like you missed a couple of things: colA is **between** F001 and F020 and **except** F010 Teethless mama wrote: > =SUMPRODUCT((ISNUMBER(MATCH(A1:A8,{"F001","F020"},)))*D1:F8) > > > "aprendiz" wrote: > >> I have an spreadsheet with columns like: >> >> col A col D col E colF >> B230 8 1 0 >> F001 -5 0 10 >> F002 3 20 0 >> F010 5 -29 3 >> F014 40 3 7 >> F020 -25 -6 -100 >> F113 0 0 0 >> H002 34 -50 23 >> in ascendent order. >> I want to be able to Sum col D to colF if colA is between F001 and F020 >> except F010. >> I am just starting with this and I really don't know how to mix and match >> all the formulas. any help will be much appreaciated!!
From: Glenn on 15 Feb 2010 11:57
And I see you realized that as I was typing my previous post. Your new solution is very similar to the one I posted to one of the OP's other identical requests. Glenn wrote: > Looks like you missed a couple of things: > > > colA is **between** F001 and F020 > > and > > **except** F010 > > > Teethless mama wrote: >> =SUMPRODUCT((ISNUMBER(MATCH(A1:A8,{"F001","F020"},)))*D1:F8) >> >> >> "aprendiz" wrote: >> >>> I have an spreadsheet with columns like: >>> >>> col A col D col E colF >>> B230 8 1 0 >>> F001 -5 0 10 >>> F002 3 20 0 >>> F010 5 -29 3 >>> F014 40 3 7 >>> F020 -25 -6 -100 >>> F113 0 0 0 >>> H002 34 -50 23 >>> in ascendent order. >>> I want to be able to Sum col D to colF if colA is between F001 and >>> F020 except F010. >>> I am just starting with this and I really don't know how to mix and >>> match all the formulas. any help will be much appreaciated!! |