Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula please
I am trying, and failing, to put together a formula to reach the following
(in the most simplist way I can explain): If cells A1 and/or A2 and/or A3 and/or A4....A7 contain the letter: "A" then = cell D32 + "B" then = cell E32 + "C" then = cell F32... through to "H"=K32 So essentually if only "B", "D" and "F" were keyed in to A2, A4, A5 (in any order and only one letter per cell), the sum total would be calculated as D32+G32+I32. Is anyone able to please advise? Much appriciated |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula please
Note that this refers to C32 (and c32 needs to be blank) in order to handle
the empty cells. =LOOKUP(A1,{0,"a","b","c","d","e","f","g","h"},C32 :K32)+LOOKUP(A2,{0,"a","b","c","d","e","f","g","h" },C32:K32)+LOOKUP(A3,{0,"a","b","c","d","e","f","g ","h"},C32:K32)+LOOKUP(A4,{0,"a","b","c","d","e"," f","g","h"},C32:K32)+LOOKUP(A5,{0,"a","b","c","d", "e","f","g","h"},C32:K32)+LOOKUP(A6,{0,"a","b","c" ,"d","e","f","g","h"},C32:K32)+LOOKUP(A7,{0,"a","b ","c","d","e","f","g","h"},C32:K32) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Peta" wrote: I am trying, and failing, to put together a formula to reach the following (in the most simplist way I can explain): If cells A1 and/or A2 and/or A3 and/or A4....A7 contain the letter: "A" then = cell D32 + "B" then = cell E32 + "C" then = cell F32... through to "H"=K32 So essentually if only "B", "D" and "F" were keyed in to A2, A4, A5 (in any order and only one letter per cell), the sum total would be calculated as D32+G32+I32. Is anyone able to please advise? Much appriciated |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula please
Hi Luke, Thanks. It is on the right track but seems to throw the correct answer out by about 1million when not all letters are included or if only one is included . Does it know to include only the specific cell in to the sum when the letter is present in the table. ie do not include E32's value in sum if "B" is not present? Thanks for helping -- Peet "Luke M" wrote: Note that this refers to C32 (and c32 needs to be blank) in order to handle the empty cells. =LOOKUP(A1,{0,"a","b","c","d","e","f","g","h"},C32 :K32)+LOOKUP(A2,{0,"a","b","c","d","e","f","g","h" },C32:K32)+LOOKUP(A3,{0,"a","b","c","d","e","f","g ","h"},C32:K32)+LOOKUP(A4,{0,"a","b","c","d","e"," f","g","h"},C32:K32)+LOOKUP(A5,{0,"a","b","c","d", "e","f","g","h"},C32:K32)+LOOKUP(A6,{0,"a","b","c" ,"d","e","f","g","h"},C32:K32)+LOOKUP(A7,{0,"a","b ","c","d","e","f","g","h"},C32:K32) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Peta" wrote: I am trying, and failing, to put together a formula to reach the following (in the most simplist way I can explain): If cells A1 and/or A2 and/or A3 and/or A4....A7 contain the letter: "A" then = cell D32 + "B" then = cell E32 + "C" then = cell F32... through to "H"=K32 So essentually if only "B", "D" and "F" were keyed in to A2, A4, A5 (in any order and only one letter per cell), the sum total would be calculated as D32+G32+I32. Is anyone able to please advise? Much appriciated |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula please
Hi Luke,
Sorry I forgot to change the formula to incue the blank cell after I added it! Thank you so much, works perfectly! -- Peet "Luke M" wrote: Note that this refers to C32 (and c32 needs to be blank) in order to handle the empty cells. =LOOKUP(A1,{0,"a","b","c","d","e","f","g","h"},C32 :K32)+LOOKUP(A2,{0,"a","b","c","d","e","f","g","h" },C32:K32)+LOOKUP(A3,{0,"a","b","c","d","e","f","g ","h"},C32:K32)+LOOKUP(A4,{0,"a","b","c","d","e"," f","g","h"},C32:K32)+LOOKUP(A5,{0,"a","b","c","d", "e","f","g","h"},C32:K32)+LOOKUP(A6,{0,"a","b","c" ,"d","e","f","g","h"},C32:K32)+LOOKUP(A7,{0,"a","b ","c","d","e","f","g","h"},C32:K32) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Peta" wrote: I am trying, and failing, to put together a formula to reach the following (in the most simplist way I can explain): If cells A1 and/or A2 and/or A3 and/or A4....A7 contain the letter: "A" then = cell D32 + "B" then = cell E32 + "C" then = cell F32... through to "H"=K32 So essentually if only "B", "D" and "F" were keyed in to A2, A4, A5 (in any order and only one letter per cell), the sum total would be calculated as D32+G32+I32. Is anyone able to please advise? Much appriciated |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula please
Hi Peta
Try =SUMPRODUCT((UPPER(A1:A8)={"A","B","C","D","E","F" ,"G","H"})*(D32:K22)) -- Regards Roger Govier "Peta" wrote in message ... I am trying, and failing, to put together a formula to reach the following (in the most simplist way I can explain): If cells A1 and/or A2 and/or A3 and/or A4....A7 contain the letter: "A" then = cell D32 + "B" then = cell E32 + "C" then = cell F32... through to "H"=K32 So essentually if only "B", "D" and "F" were keyed in to A2, A4, A5 (in any order and only one letter per cell), the sum total would be calculated as D32+G32+I32. Is anyone able to please advise? Much appriciated |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula please
Glad you got it working. Note that Roger's formula will work as well (and
probably more efficient), but he has a small typo. Should be =SUMPRODUCT((A1:A8={"A","B","C","D","E","F","G","H "})*(D32:K32)) Note that I removed the UPPER function, as its not really needed. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Peet" wrote: Hi Luke, Sorry I forgot to change the formula to incue the blank cell after I added it! Thank you so much, works perfectly! -- Peet "Luke M" wrote: Note that this refers to C32 (and c32 needs to be blank) in order to handle the empty cells. =LOOKUP(A1,{0,"a","b","c","d","e","f","g","h"},C32 :K32)+LOOKUP(A2,{0,"a","b","c","d","e","f","g","h" },C32:K32)+LOOKUP(A3,{0,"a","b","c","d","e","f","g ","h"},C32:K32)+LOOKUP(A4,{0,"a","b","c","d","e"," f","g","h"},C32:K32)+LOOKUP(A5,{0,"a","b","c","d", "e","f","g","h"},C32:K32)+LOOKUP(A6,{0,"a","b","c" ,"d","e","f","g","h"},C32:K32)+LOOKUP(A7,{0,"a","b ","c","d","e","f","g","h"},C32:K32) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Peta" wrote: I am trying, and failing, to put together a formula to reach the following (in the most simplist way I can explain): If cells A1 and/or A2 and/or A3 and/or A4....A7 contain the letter: "A" then = cell D32 + "B" then = cell E32 + "C" then = cell F32... through to "H"=K32 So essentually if only "B", "D" and "F" were keyed in to A2, A4, A5 (in any order and only one letter per cell), the sum total would be calculated as D32+G32+I32. Is anyone able to please advise? Much appriciated |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula please
Thanks for picking up on the typo of K22 instead on K32, Luke.
You're quite right, the Upper function isn't necessary. I was amending the formula posted from one where I was converting letters to numbers, and had to force upper case. I removed the Code part, but forgot to remove the Upper function. -- Regards Roger Govier "Luke M" wrote in message ... Glad you got it working. Note that Roger's formula will work as well (and probably more efficient), but he has a small typo. Should be =SUMPRODUCT((A1:A8={"A","B","C","D","E","F","G","H "})*(D32:K32)) Note that I removed the UPPER function, as its not really needed. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Peet" wrote: Hi Luke, Sorry I forgot to change the formula to incue the blank cell after I added it! Thank you so much, works perfectly! -- Peet "Luke M" wrote: Note that this refers to C32 (and c32 needs to be blank) in order to handle the empty cells. =LOOKUP(A1,{0,"a","b","c","d","e","f","g","h"},C32 :K32)+LOOKUP(A2,{0,"a","b","c","d","e","f","g","h" },C32:K32)+LOOKUP(A3,{0,"a","b","c","d","e","f","g ","h"},C32:K32)+LOOKUP(A4,{0,"a","b","c","d","e"," f","g","h"},C32:K32)+LOOKUP(A5,{0,"a","b","c","d", "e","f","g","h"},C32:K32)+LOOKUP(A6,{0,"a","b","c" ,"d","e","f","g","h"},C32:K32)+LOOKUP(A7,{0,"a","b ","c","d","e","f","g","h"},C32:K32) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Peta" wrote: I am trying, and failing, to put together a formula to reach the following (in the most simplist way I can explain): If cells A1 and/or A2 and/or A3 and/or A4....A7 contain the letter: "A" then = cell D32 + "B" then = cell E32 + "C" then = cell F32... through to "H"=K32 So essentually if only "B", "D" and "F" were keyed in to A2, A4, A5 (in any order and only one letter per cell), the sum total would be calculated as D32+G32+I32. Is anyone able to please advise? Much appriciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|