lookup and multiple criteria

A

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

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

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

macropod said:
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 said:
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!!
Click to expand...
.
Click to expand...
 
A

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

aprendiz said:
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 said:
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 said:
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!!
Click to expand...
.
Click to expand...
Click to expand...
 
A

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

macropod said:
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 said:
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!!
Click to expand...
.
Click to expand...
 
M

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]


aprendiz said:
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 said:
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 said:
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!!
Click to expand...
.
Click to expand...
Click to expand...
 
A

aprendiz

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

macropod said:
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 said:
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 said:
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!!
.
Click to expand...
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

vlookup and multiple criteria 5
Looups and multiple criteria 1
lookup to multiple rows and columns 1
How to insert row on Col A field change 2
Counting with multiple criteria in separate Columns 4
Lookup value and return column offset value 6
Lookup 0
Complex MAX and Lookup 5

Top