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

lookup and multiple criteria
 

Hi aprendiz,

Try:
=SUM(IF((A1:A10="F001")*(A1:A10<="F999")*(A1:A10< "F010"),D1:F10))
as an array formula (entered with Ctrl-Shift-Enter).


--
Cheers
macropod
[Microsoft MVP - Word]


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

lookup and multiple criteria
 

Thanks macropod for answering so quickly.
This formula is not working for me. I have 1700 raws similar to the table I
posted and when I use your formula is says that it is a circular reference
therefore doesn't evaluate at all, and everything gives a False result.
I would appreciate if you can point me to any other solution.
Thanks again.
aprendiz

"macropod" wrote:

Hi aprendiz,

Try:
=SUM(IF((A1:A10="F001")*(A1:A10<="F999")*(A1:A10< "F010"),D1:F10))
as an array formula (entered with Ctrl-Shift-Enter).


--
Cheers
macropod
[Microsoft MVP - Word]


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

lookup and multiple criteria
 

Just to clarify, I need a total sum of the cells D, E and F if the cell in
column A i

I need to sum all the cells from D, E and F for F001 to F020 except the
cells for F010.

aprenidz

"aprendiz" wrote:

Thanks macropod for answering so quickly.
This formula is not working for me. I have 1700 raws similar to the table I
posted and when I use your formula is says that it is a circular reference
therefore doesn't evaluate at all, and everything gives a False result.
I would appreciate if you can point me to any other solution.
Thanks again.
aprendiz

"macropod" wrote:

Hi aprendiz,

Try:
=SUM(IF((A1:A10="F001")*(A1:A10<="F999")*(A1:A10< "F010"),D1:F10))
as an array formula (entered with Ctrl-Shift-Enter).


--
Cheers
macropod
[Microsoft MVP - Word]


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

lookup and multiple criteria
 

macropod,
your answer totally answered my question. it was my fault, I was replacing
the formula wrongly.

I have another question related to this one, I need to evaluate something
similar but with one more criteria:

col A col D col E colF colG
B230 8 1 0
F001 -5 0 10 xx
F002 3 20 0
F010 5 -29 3
F014 40 3 7 xx
F020 -25 -6 -100
F113 0 0 0
H002 34 -50 23

I need to sum the same as before but only if colG = "xx".
Thanks in advance! you saved my day!

Aprendiz

"macropod" wrote:

Hi aprendiz,

Try:
=SUM(IF((A1:A10="F001")*(A1:A10<="F999")*(A1:A10< "F010"),D1:F10))
as an array formula (entered with Ctrl-Shift-Enter).


--
Cheers
macropod
[Microsoft MVP - Word]


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

lookup and multiple criteria
 

Hi aprendiz,

Try:
=SUM(IF((A1:A10="F001")*(A1:A10<="F999")*(A1:A10< "F010")*(G1:G10="xx"),D1:F10))
as an array formula (entered with Ctrl-Shift-Enter).


--
Cheers
macropod
[Microsoft MVP - Word]


"aprendiz" wrote in message ...
macropod,
your answer totally answered my question. it was my fault, I was replacing
the formula wrongly.

I have another question related to this one, I need to evaluate something
similar but with one more criteria:

col A col D col E colF colG
B230 8 1 0
F001 -5 0 10 xx
F002 3 20 0
F010 5 -29 3
F014 40 3 7 xx
F020 -25 -6 -100
F113 0 0 0
H002 34 -50 23

I need to sum the same as before but only if colG = "xx".
Thanks in advance! you saved my day!

Aprendiz

"macropod" wrote:

Hi aprendiz,

Try:
=SUM(IF((A1:A10="F001")*(A1:A10<="F999")*(A1:A10< "F010"),D1:F10))
as an array formula (entered with Ctrl-Shift-Enter).


--
Cheers
macropod
[Microsoft MVP - Word]


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

lookup and multiple criteria
 

Thanks again macropod. your answer was spot on! Thanks!

"macropod" wrote:

Hi aprendiz,

Try:
=SUM(IF((A1:A10="F001")*(A1:A10<="F999")*(A1:A10< "F010")*(G1:G10="xx"),D1:F10))
as an array formula (entered with Ctrl-Shift-Enter).


--
Cheers
macropod
[Microsoft MVP - Word]


"aprendiz" wrote in message ...
macropod,
your answer totally answered my question. it was my fault, I was replacing
the formula wrongly.

I have another question related to this one, I need to evaluate something
similar but with one more criteria:

col A col D col E colF colG
B230 8 1 0
F001 -5 0 10 xx
F002 3 20 0
F010 5 -29 3
F014 40 3 7 xx
F020 -25 -6 -100
F113 0 0 0
H002 34 -50 23

I need to sum the same as before but only if colG = "xx".
Thanks in advance! you saved my day!

Aprendiz

"macropod" wrote:

Hi aprendiz,

Try:
=SUM(IF((A1:A10="F001")*(A1:A10<="F999")*(A1:A10< "F010"),D1:F10))
as an array formula (entered with Ctrl-Shift-Enter).


--
Cheers
macropod
[Microsoft MVP - Word]


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

.