VLookup to return a YTD sum

[barnett2000]

I have data in a sheet for the months. I want the user to be able to put in the number of the month they want (i.e. "4" for "April") in a cell, and have a formula return a value for the Month and YTD amount (i.e. January-April).

I don't want to have 12 different columns for YTD numbers, so I'd like the formula to look at the month the user input then use that to determine the YTD amount.

Maybe Vlookup isn't the best way. Any suggestions would be appreciated.

Sample file is attached (note, this is a very simplified example of what I need. The actual data has hundreds of lines.).

Using Excel 2010.

Thanks in advance!
David

[Debraj Roy]

In C4, use formula as..

Please Login or Register  to view this content.

Drag down for other results..

[barnett2000]

I may have simplified my example too much.

The formula will need to do a sum from data on another tab, which may or may not have the same number of rows. For example, in my example sheet, "Revenue" may actually be an account number like 123-04, which may or may not exist on the other tab. The formula will need to look for the account in A4 in another tab, return "0" if it doesn't find it (I'll use an IF(ISNA) for that) and return the sum if it does. Attached is a more accurate sample.

[azumi]

=SUM(($A$12:$A$14=$A4)*(MONTH($B$10:$M$10&1)<=$C$1)*($B$12:$M$14))

this array formula, need to press CTRL-SHIFT-ENTER button together, and copied down or find the file attach

[Debraj Roy]

Another approach..


In C8

Please Login or Register  to view this content.

[barnett2000]

Debraj Roy, I think that's exactly what I need. Thanks!

[Debraj Roy]

Thanks for the feedback ..

[veganvonteese]

Hi there

Thanks Barnett2000 for submitting this question, I have a similar question but instead of using an integer for the months, what if it is in the format 31/1/18, 28/2/18, 31/3/18 for both the lookup as well as the range? Or must you use month function?

[FlameRetired]

Hi veganvonteese. Welcome to the forum.

Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.