ExcelBanter - Looups and multiple criteria

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Looups and multiple criteria (https://www.excelbanter.com/excel-worksheet-functions/256322-looups-multiple-criteria.html)

Looups and multiple criteria

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

Looups and multiple criteria

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

Something like this:

=SUMPRODUCT((LEFT(A1:A8,1)="F")*
(--(RIGHT(A1:A8,3))<=20)*
(--(RIGHT(A1:A8,3))=1)*
(--(RIGHT(A1:A8,3))<10),
D1:D8+E1:E8+F1:F8)