Using SumIfs to change range based on dropdown

[rgold]

Hello,
I am trying to write a specific sumifs formula with several nested states based on the following example scenario.

On Sheet 1: Drop down menu with months of year
On Sheet 2:
Jan: 1
Feb: 2
Mar: 3
Apr: 0

I would like to write a formular to sum a YTD value. For example, if the drop down month menu is March, I would like to sum only Jan-Mar data.

[DonkeyOte]

The layout of your data is not clear both in terms of physical ranges and underlying data types.... I would suggest that you post a sample file that accurately reflects the set up you are working with so people may better assist.

[rgold]

Hello DonkeyOte,
I will try to post a sample file and see if that helps. Thanks.

[rgold]

Hello,
I am trying to write a specific sumifs formula to pull in and reflect YTD data. In the attached file there are three tabs. My question deals with the first two which are called Dashboard and Data Upload.

If you go to the dashboard tab, you will notice up at the top a dropdown.."Select Month View". This is pulling the specific month of data from the Data Upload tab and dropping in the corresponding block on the Dashboard tab. There is a YTD selection in the dropown. Upon selection, I want it to only pull the YTD months for both actual and target data. The problem is this:

- If I put a target in from Jan - Dec, but only have Jan - Aug data to report, a YTD data pull will reflect Jan-Aug actual data, but Jan-Dec data for the target. I want the YTD calculation to change with every new month of data added, but only calculate the target YTD that is aligned with the last month of data added. So, if we are putting Aug data in, I want the YTD drop down to calculate:

YTD Target (Jan-Aug) as compared to YTD Actual (Jan-Aug)

Hope my explanation helps. Thanks.

[DonkeyOte]

If I put a target in from Jan - Dec, but only have Jan - Aug data to report, a YTD data pull will reflect Jan-Aug actual data, but Jan-Dec data for the target.

In your sample it's not very clear where "Actuals" are entered.

The data in the sample implies you have 12 months of actuals - if "Current" is meant to represent Actual entries of course.
Generally speaking we would expect Blanks where no actuals exist.

To avoid confusion could you possibly post a version that reflects the Jan-Aug scenario you outline in your prior post ?

[rgold]

"Current" is "Actual". I agree with your statement about there being blanks where no data is available to report. The issue I am trying to get around is if targets are placed in every month (Jan-Dec) a calculated YTD for the target will be based on more months than what is currently reported.

[DonkeyOte]

the above does not answer the question as to how you intend to determine "last actual"