Formula to get current moth daily totals by day of month

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,602
Office Version
  1. 365
Platform
  1. Windows
HI all

Is there a formula that can populate the current months table with daily totals taking the data from a range?

so, I have a simple table with days 1-31 listed and I'd like the daily total from the current month populated into each day.

for example, the data table looks like this...

DATEMONTHQSALES
01-Nov-13Nov3
02-Nov-13Nov320
03-Nov-13Nov3
04-Nov-13Nov3
05-Nov-13Nov3325
06-Nov-13Nov3250


..and the output table like this.....

DAYTOTALS
1
2
3
4
5


So if the current month was NOV (2013) , I'd like the daily total sales to be plaed in each of the day rows in the out put table using a formula..
In my examples above the result required would look like the following....

DAYTOTALS
1
220
3
4
5325
6250
7


Hope this makes sense and appreciate in advance any help
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Obviously this month is Feb 2022 not Nov 2013 so what do you mean by the "current month"?
 
Upvote 0
Obviously this month is Feb 2022 not Nov 2013 so what do you mean by the "current month"?
Yes, I understand the example shown is showing 'old' data from NOV13 but the required results would check the data and give the daily sales for the current month...

so I guess if the data was showing the following....

DATEMONTHQSALES
01-Feb-22Feb4
02-Feb-22Feb420
03-Feb-22Feb4
04-Feb-22Feb4
05-Feb-22Feb4325
06-Feb-22Feb4250
07-Feb-22Feb4


then the required output for the current month would be...

DAYTOTALS
1
220
3
4
5325
6250
7


sorry for confusion
 
Upvote 0
Cell Formulas
RangeFormula
F1F1=TODAY()
G3:G33G3=SUMPRODUCT($D$2:$D$104,--(MONTH($A$2:$A$104)=MONTH($F$1)),--(YEAR($A$2:$A$104)=YEAR($F$1)),--(DAY($A$2:$A$104)=F3))
A3:A86A3=A2+1
 
Upvote 0
Another option
+Fluff 1.xlsm
ABCDEFGHI
1DATEMONTHQSALESDAYTOTALS
201/02/2022Feb410
302/02/2022Feb420220
403/02/2022Feb430
504/02/2022Feb440
605/02/2022Feb43255325
706/02/2022Feb42506250
807/02/2022Feb470
Data
Cell Formulas
RangeFormula
I2:I8I2=SUMIFS($D$2:$D$8,$A$2:$A$8,DATE(YEAR(TODAY()),MONTH(TODAY()),H2))
 
Upvote 0
Solution
Another option
+Fluff 1.xlsm
ABCDEFGHI
1DATEMONTHQSALESDAYTOTALS
201/02/2022Feb410
302/02/2022Feb420220
403/02/2022Feb430
504/02/2022Feb440
605/02/2022Feb43255325
706/02/2022Feb42506250
807/02/2022Feb470
Data
Cell Formulas
RangeFormula
I2:I8I2=SUMIFS($D$2:$D$8,$A$2:$A$8,DATE(YEAR(TODAY()),MONTH(TODAY()),H2))

Great!

That's perfect - thank you

Thanks also @JamesCanale for your help
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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
Back
Top