index, sumproduct, or other


J

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
 
Ad

Advertisements

T

T. Valko

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

Advertisements

A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top