lookup and multiple criteria

[aprendiz]

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

 

[macropod]

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).

 

[aprendiz]

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

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]

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

.

 

[aprendiz]

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

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

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]

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

.

 

[aprendiz]

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

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]

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

.

 

[macropod]

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]

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

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]

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

.

 

[aprendiz]

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

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]

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

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]


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

.