If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Complex SUMPRODUCT/VLOOKUP/SUMIF
Hi there, hope someone can help.
I have a summary table within a spreadsheet where I am tracking actual and forecast spend against individual projects. I would like to lookup the project id in my detail, sum the corresponding figures based on whether or not the figures are actuals or forecast figures, and return actual spend in a cell in my summary table. An example would be in cell D4, looking up CR111 in the detail (row 18), summing U:AF based on the row (18) and whether or not the column headers in U16:AF16 contain an A (for actuals) or F (for forecast). Hopefully this is clear! If anyone can help I would appreciate it. Thanks |
#2
|
|||
|
|||
Complex SUMPRODUCT/VLOOKUP/SUMIF
Works like a dream! Corrected an ommission in the formula (missed out a comma).
=SUMIF($U$16:$AF$16,$E$4,INDEX($U$18:$AF$100,MATCH ($D4,$T$18:$T$100,0),)) Thanks a lot! "Roger Govier" wrote: Hi One way Assuming A or F in cell E4 Code required in D4 Column T contains codes Block of values extends between U18 and AF100 =SUMIF($U$16:$AF$16,$E$4,INDEX($U$18:$AF$100,MATCH ($D4,$T$18:$T$100,0),)) Adjust ranges to suit. -- Regards Roger Govier "tigger" wrote in message ... Hi there, hope someone can help. I have a summary table within a spreadsheet where I am tracking actual and forecast spend against individual projects. I would like to lookup the project id in my detail, sum the corresponding figures based on whether or not the figures are actuals or forecast figures, and return actual spend in a cell in my summary table. An example would be in cell D4, looking up CR111 in the detail (row 18), summing U:AF based on the row (18) and whether or not the column headers in U16:AF16 contain an A (for actuals) or F (for forecast). Hopefully this is clear! If anyone can help I would appreciate it. Thanks |
Thread Tools | |
Display Modes | |
|
|