Can anyone recommend a easy way to change a date range in a SUMIFS function?

franklingreen

I have a SHEET where the columns need to reference a progressing month date range in a SUMIFS function. Is there an easier way to use a function other than referencing two specific date ranges in a different sheet? My formula is the following:

=SUMIFS({450 - INVOICE ENTRY SHEET Range 1}, {450 - INVOICE ENTRY SHEET Range 2}, <={425 - CASH FLOW META DATA Range 1}, {450 - INVOICE ENTRY SHEET Range 2}, >={425 - CASH FLOW META DATA Range 2}, {450 - INVOICE ENTRY SHEET Range 3}, =$[COST CODE LEVEL]@row)

For every new column I add, I have to manually change the references to the date range. Is there a method to progressively index a month range?

Paul Newcome

Are you able to provide some screenshots for context?

franklingreen

2024-01-25_14-51-21.jpg

I have attached an image of a part of the SHEET. The months carry out about 100 more. The column with "ACTUAL" has the formula that needs to be specific to the month of that column. I currently have to manually change every columns reference to another sheet with a start and end day for each month.

Thank you for your support.

franklingreen

Sheet that is being reference for the date values

Andrée Starå

Hi @franklingreen

I hope you're well and safe!

Would something like this work, and would you update your formula to check the Year, Month, and so on in the main sheet instead?

• I recommend adding a Sheet Summary Field with the Year and referencing it in the formula.
• What I usually do in my client solutions, which have Year, Months, Days, or similar, is to add so-called helper rows somewhere in the sheet (usually at the top) with the YYear in each cell, Month#, and so on, and I would still put the Year in the Sheet Summary and reference it in the Year row at the top)

Make sense?

Would any of those options work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!