Hello all,
We were initially going to use Power BI for a Dashboard project we are working on, but thanks to their latest update, we don't think it'll be a viable option in time. So in the meantime, we are using excel as a workaround.
In a nutshell: we get weekly reports submitted through MS Forms for some divisions, which we have automated to import into an Excel table. The problem with weekly reports is that it throws off numbers if you want to try to view them by month. To remedy this, we have a "Week Start Date" and "Week End Date", and if the beginning/ end of the week falls on the beginning/ end of the month, you'd submit two reports. While we are pushing for daily reports to avoid this, it isn't super popular just yet. We want them to be able to see what the numbers for the week, and the month are.
What I can't figure out is how to make Excel SUMIFS a column if a week falls between two months. This is the current formula:
=SUMIFS(K3:K2000,$G$3:$G$2000,$G$1,Inputs!$I$3:$I$2000,$I$1)
K Column is the range we need added, G column is the Week Start Date entry, G1 is the start date to "filter", I column is the Week End Date entry, and I1 is the end date to "filter". G1 and I1 are dynamic values from another sheet in the workbook, so by changing those dates it should update the rest of the graphs and charts on the "dashboard". This is what the entry table looks like.
My basic question is: how do I SUMIFS any entry in column K for any entry that falls between the values in G1 and I1 so that I could capture both month and weekly numbers?
Accepted Solutions