| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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!! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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!! . |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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!! . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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!! . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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!! . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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!! . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lookup using multiple criteria | Excel Worksheet Functions | |||
| Lookup using multiple sheets and multiple criteria, sorry if 2 pos | Excel Worksheet Functions | |||
| Lookup using multiple sheets and multiple criteria | Excel Discussion (Misc queries) | |||
| Lookup on multiple criteria and max | Excel Worksheet Functions | |||
| Lookup with multiple criteria... please help! | Excel Worksheet Functions | |||
Linear Mode
