Match?,Index?, Sum? or some other way?

[Sue]

Currently have this formula which works great.
{=SUM(IF(DETAIL!$B$3:$B$9609='YTD-BR'!$A6,IF(DETAIL!$C$3:$C$9609='YTD-BR'!C$3,DETAIL!E$3:$E$9609,0),0))}

But that only works if my numbers I am pulling from are always on Detail in
column E3:E9609

The twist is I would like for the formula to also look at Cell 'YTD-BR'!$C1
, where the month name Jan will be entered and then go to worksheet Detail
and find the column that has Jan heading. (Jan - Dec will be in Cells E1 thu
P1 on Detail) and return the numbers in that months column to the YTD-BR
worksheet.

I am lost at this point and would appreciate help. Thanks in Advance.

 

[Luke M]

Use the offset function.

=SUM(OFFSET(Detail!$E$3,0,MATCH('YTD-BR'!$C1 ,Detail!E11,0)-1,9607,1))

Note that the 9607 near the end needs to be equal to number of rows of data
that you want to look in.

 

[T. Valko]

Try this:

Normally entered...

=SUMPRODUCT(--(Detail!$B$3:$B$9609='YTD-BR'!$A6),--(Detail!$C$3:$C$9609='YTD-BR'!C$3),INDEX(Detail!$E$3:$P$9609,,MATCH('YTD-BR'!$C1,Detail!$E$1:$P$1,0)))

 

[Sue]

Use the offset function.

=SUM(OFFSET(Detail!$E$3,0,MATCH('YTD-BR'!$C1 ,Detail!E11,0)-1,9607,1))

Note that the 9607 near the end needs to be equal to number of rows of data
that you want to look in.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

Unfortunately this returned the total for the entire column and did not
bring in the individual cells

 

[Domenic]

Try...

=SUM(IF(DETAIL!$B$3:$B$9609='YTD-BR'!$A6,IF(DETAIL!$C$3:$C$9609='YTD-BR'!
C$3,INDEX(DETAIL!E$3:$P$9609,0,MATCH('YTD-BR'!$C1,DETAIL!E$1:$P$1,0)))))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

http://www.xl-central.com