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!!

=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!!

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!!

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!!

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!!

Try this...

Table in the range A2:D9

Lookup values:

F2 = F001
G2 = F020
H2 = F010 (values to exclude)

=SUM(INDEX(B2:D9,MATCH(F2,A2:A9,0),0):INDEX(B2:D9, MATCH(G2,A2:A9,0),0))-SUMPRODUCT((A2:A9=H2)*B2:D9)

--
Biff
Microsoft Excel MVP


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