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

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