index, sumproduct, or other

[JustBreathe]

I'm trying to write a formula that will pull the YTD total for the month of
interest. The problem is each month has two columns, MTD and YTD. When I
use an index formula it pulls the MTD total which is the first of the two
columns. I am having little luck with writing a sumproduct formula that will
work either. Any suggestions here? (Changing the headers naming convention
is really not an option because it will just revert back each time the data
extract which creates this report is refreshed.)

A B C D E
Jan-09 Jan-09 Feb-09 Feb-09
MTD YTD MTD YTD
111 $5 $5 $3 $8
222 $1 $1 $4 $5
Total $6 $6 $7 $13

 

[T. Valko]

Do you want the result to come from the "Total" row or one of the "item"
rows?

 

[smartin]

I'm trying to write a formula that will pull the YTD total for the month of
interest. The problem is each month has two columns, MTD and YTD. When I
use an index formula it pulls the MTD total which is the first of the two
columns. I am having little luck with writing a sumproduct formula that will
work either. Any suggestions here? (Changing the headers naming convention
is really not an option because it will just revert back each time the data
extract which creates this report is refreshed.)

A B C D E
Jan-09 Jan-09 Feb-09 Feb-09
MTD YTD MTD YTD
111 $5 $5 $3 $8
222 $1 $1 $4 $5
Total $6 $6 $7 $13

With the Total in row 5 and the month of interest in Y1 (in mm/1/yyyy
form (not format)):

=INDEX($B$5:$E$5,1,1+MATCH($Y$1,$B$1:$E$1,0))

Or perhaps this array* formula, where Z1 is just a month number 1-12:

=INDEX($B$5:$E$5,1,1+MATCH($Z$1,MONTH($B$1:$E$1),0))

*Commit the array formula by pressing Ctrl+Shift+Enter, do not just
press Enter or Tab.

 

[Ashish Mathur]

Hi,

You could try this. This will return the result from the total row

=INDEX($B$1:$E$5,MATCH("Total",A1:A5,0),MATCH(A8,B1:E1,0)+1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com