How to simplify SUMIF formula

Jo Kuhn

Hi!

I need to pull data from about 40 core sheets into a monthly budget sheet. Each core sheet will have it's own row on the budget sheet and the header columns will be displayed January 2025 through December 2025. The formula I have works, it sums the budget correctly and places a "-" if the sum is $0.00, but to reference each sheet and change the dates in each cell over the entire year is tedious and time consuming. Do you have any ideas for a simpler formula that might allow me to drag a formula across a row and pull in what we need for each month without having to manually revise each cell?

=IF(SUMIFS({USD Amount}, {Date}, >=DATE(2025, 1, 1), {Date}, <=DATE(2025, 1, 31)) = 0, "-", SUMIFS({USD Amount}, {Date}, >=DATE(2025, 1, 1), {Date}, <=DATE(2025, 1, 31)))

kowal

hi @Jo Kuhn,

maybe you can keep the year in sheet summary so instead of writing 2025 multiple times you cna just connect it with value from Sheet Summary and when you will change the value ther it will change everywhere in formula. You can try the same for date.

Kelly Moore

Hey @Jo Kuhn

Does your license give you access to the Pivot App? With a couple of helper columns in your source sheets, you could simplify your process of gathering the data for the summary sheet.

If your data is being used for chart generation, you could use a report instead of your Summary sheet to gather your data. This would also need a few helper columns on the source sheets. The disadvantage is that seeing the budget over time is not straightforward.

If the year is always the current year, you can use this formula.

=IF(SUMIFS({USD Amount}, {Date}, AND(ISDATE(@cell), YEAR(TODAY())=YEAR(@cell), MONTH(@cell)=1))=0, "-", SUMIFS({USD Amount}, {Date}, AND(ISDATE(@cell), YEAR(TODAY())=YEAR(@cell), MONTH(@cell)=1))

Let me know if you have any questions

Kelly

Jo Kuhn

Hi Kowal and Kelly,

Thank you for taking the time to work through a couple of solutions. With some trial and error, we've added two rows to the top of our sheet; a row for the number corresponding with the month and a row for the year. After we update the references in the January column, we can then simply drag across and the formula updates for the rest of the year. So far, this is working well for us, but in the meantime, I'll also check out the Pivot App.

=IF(SUMIFS({USD Amount}, {Date}, AND(IFERROR(MONTH(@cell ), 0) = January3, IFERROR(YEAR(@cell ), 0) = January4)) = 0, "-", SUMIFS({USD Amount}, {Date}, AND(IFERROR(MONTH(@cell ), 0) = January3, IFERROR(YEAR(@cell ), 0) = January4)))