Returning cell values based on date.

[Krucial155]

Maybe I've backed myself into a corner? I track social media metrics. One metric is simple, total followers. Each day we input a new number and that is automated to show up in a table elsewhere. Basically, somebody should be able to input today's data, and then later on the page copy and paste a simple report to send off to the hunchos. =LOOKUP(2,1/(3:3<>""),3:3) is the what I use to return the last non-blank cell, which will always work because we don't input data for days in the future. However, I recently added "Weekly Totals" where it's simply =Sum and with today's data plus the past 6 days. For example AB19 has =sum(ab17:v17) and that brings back the total for the week. However, I can't use the same function I use for totals because in AC, it is still populating with the 0s in the cells that are blank and will return it until 7 days in the future.

When trying to pull values for my weekly metrics, how can I get it to stop on Today's date, and not pull from the future dates due to my bad formulas. Do I need to redo how I'm totaling my 7 day totals? Ideally I want a function that could also work for the weekly percentage cells too. My fear is that I backed myself into a corner and need to change how my weekly totals and percentages are calculated to work with a different function that will automate the process.

oh and ignore AB3 4 and 5. We only update that cell at the end of the day, before we send off that report still though