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

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" <(E-Mail Removed)> wrote in message
> news:7AFE90FE-E1DC-4BC9-8517-(E-Mail Removed)...
> > 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
>