Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help Needed
My data table is like this:
Symbol Total 549S0A1 919S0B1 919S0A1 Total 245216 44296 41402 16053 QQQQ 20478 1009 2837 1558 MSFT 15754 2107 1857 250 SPY 5314 1143 198 AAPL 5212 231 1167 331 I am looking for a formula to put into the 3rd row below that will take the value in row 1 and row 2 and return the corresponding value in the table above - the result is shown below as well. For row 2 below, I was hoping the formula would be able to look at just the first 3 characters of the table above - so if there are multiple instances, the formula will sum up the values. QQQQ 919 =2837+1558 (4395) Thank you in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help Needed
You can see some examples of the INDEX and MATCH on Debra Dalgleish site:
http://contextures.com/xlFunctions03.html The examples 2 and 3 for INDEX and MATCH show how to extract single values from a table with a similar layout. What it is more complex is getting the sum of the result with a simple formula, you may need to do it in two steps (getting the values for the individual codes and adding up) Hope this helps, Miguel. "carl" wrote: My data table is like this: Symbol Total 549S0A1 919S0B1 919S0A1 Total 245216 44296 41402 16053 QQQQ 20478 1009 2837 1558 MSFT 15754 2107 1857 250 SPY 5314 1143 198 AAPL 5212 231 1167 331 I am looking for a formula to put into the 3rd row below that will take the value in row 1 and row 2 and return the corresponding value in the table above - the result is shown below as well. For row 2 below, I was hoping the formula would be able to look at just the first 3 characters of the table above - so if there are multiple instances, the formula will sum up the values. QQQQ 919 =2837+1558 (4395) Thank you in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help Needed
Assuming that A1:E6 contains the data, A10 contains QQQQ, and A11
contains 919, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =SUM(IF(A3:A6=A10,IF(LEFT(C1:E1,LEN(A11))+0=A11,C3 :E6))) Hope this helps! In article , carl wrote: My data table is like this: Symbol Total 549S0A1 919S0B1 919S0A1 Total 245216 44296 41402 16053 QQQQ 20478 1009 2837 1558 MSFT 15754 2107 1857 250 SPY 5314 1143 198 AAPL 5212 231 1167 331 I am looking for a formula to put into the 3rd row below that will take the value in row 1 and row 2 and return the corresponding value in the table above - the result is shown below as well. For row 2 below, I was hoping the formula would be able to look at just the first 3 characters of the table above - so if there are multiple instances, the formula will sum up the values. QQQQ 919 =2837+1558 (4395) Thank you in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help Needed
=SUMPRODUCT((LEFT(C1:E1,3)=""&A10)*(A3:A6=A9)*(C3: E6))
where A1:E6 is the table, A9 holds QQQQ and A10 holds 919 Regards, Peo Sjoblom "carl" wrote: My data table is like this: Symbol Total 549S0A1 919S0B1 919S0A1 Total 245216 44296 41402 16053 QQQQ 20478 1009 2837 1558 MSFT 15754 2107 1857 250 SPY 5314 1143 198 AAPL 5212 231 1167 331 I am looking for a formula to put into the 3rd row below that will take the value in row 1 and row 2 and return the corresponding value in the table above - the result is shown below as well. For row 2 below, I was hoping the formula would be able to look at just the first 3 characters of the table above - so if there are multiple instances, the formula will sum up the values. QQQQ 919 =2837+1558 (4395) Thank you in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
continuous sum formula needed | Excel Discussion (Misc queries) | |||
Formula Help Needed | Excel Discussion (Misc queries) | |||
More Help Needed with Count formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Help! Formula needed. | Excel Discussion (Misc queries) |