Help with Excel formula

[ashliiii]

I have an Excel sheet in which col A contains numbers a1 = 1, a2 = 2, a3 = 3 etc etc.
Col B contains the dates, b1 = 6/1/21, b2 = 6/2/21 etc.
Col C contains the daily steps walked such as 9520, 8670 etc.
Col D contains the average daily step count.
On cell E1, I have the formula =INDEX(D:D,MATCH(TODAY(),B:B,0)) * INDEX(A:A,MATCH(TODAY(),B:B,0)) which gives the sum of the steps from 6/1/21 till today. How would I modify the formula to compute the average steps walked only in July of 21 updated automatically. Thank you for the time.

 

[severynm]

What version of office are you using? If you're on anything other than 365, there's a variety of options available to you. One of the methods could be to create a helper column to pull out the month (i.e. in col F =MONTH(B:B)) and then for your sum, =SUMIF(F:F,6,C:C). You could probably combine that all together with some fancy sumproduct logic to not need a helper column, but whatever you prefer.

If you're using M365 there are even more options available, some of which are more streamlined. You can make use of the Filter function, which makes the formula a little simpler and does not require a second column: =SUM(FILTER(C:C,MONTH(B:B)=6))


However, another thought. As an alternative you can also add this data to a pivot table, and all of these calculations can be taken care of automatically for you.