Date Formula - Counting Based on Days Remaining the Week

[bdav1216]

I have a pivot table that looks like this:

Date Count
7/15/2019 2
7/16/2019 2
7/17/2019 6
7/18/2019 4
7/19/2019 6
Grand Total 20

In the 'Date' field, i have a filter for 'this week' to account for only the days this week.

On another tab, I reference the Grand Total of '20', but I want to be updated to only count the current day and any remaining days in the week from this pivot table.

Is there a way to update the formula to only take the current day and the remaining days in the week? On the Monday (1st day) of each week, it would calculate a value of '20', '18' on Tuesday.....'6' on the last day of the week. The following 'week', the grand total is all days in the week.

Current Formula: =SHEET1!$A$6

[benishiryo]

if your pivot will only have a week's data, then:
=SUMIF(Sheet1!A:A,">="&TODAY(),Sheet1!B:B)

if you don't even want to filter, then:
=SUMIFS(Sheet1!B:B,Sheet1!A:A,">="&TODAY(),Sheet1!A:A,"<="&TODAY()-WEEKDAY(TODAY(),2)+7)

if it doesn't work, upload a sample file and input my formula. we'll tell you what went wrong

[bdav1216]

I attached a sample of how my data would work. Sheet2 needs the calculation applied, Sheet1 has the raw data example. In Sheet1, I will always have 5 data points.

[JeteMc]

Adjusting benishiryo's first formula to suite the "table" on sheet1 seems to work fine:

Please Login or Register  to view this content.

Let us know if you have any questions.