Help with Excel formula

ashliiii

Board Regular
Joined
Feb 27, 2011
Messages
63
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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

severynm

Board Regular
Joined
Jan 8, 2021
Messages
220
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,140,925
Messages
5,703,206
Members
421,281
Latest member
mfarhankhan87

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top