Sum Formula Needed Across Months by # of Days

[BrettOlbrys1]

Hello. In column A I have data I want to sum and in column B I have the corresponding date. For example:

7	9/1/21
3	9/2/21
20	9/23/21
5	10/3/21
6	10/5/21

Since today is 10/14/21, I want to sum and compare only the cells in column A that are within the first 14 days of the month. So I would see that for September, the sum would be 10 and for October it would be 11. This needs to be dynamic because whenever I run the report, today's date will be the day that I compare the current month to vs the prior month (based on the number of days in the month today is).

 

[queuesmef]

I would add 2 columns after your data. 1) DateDiff (that will calc the difference in days & 2) Filter (an IF formula where it will put the word "Add" or a blank cell depending on if it is within the 14 days) 3) a spot to put in today's date (i used the =Today() formula in that cell. The DateDiff formula will be: =DATEDIF(B2,$E$1,"D") . The Filter formula is =IF(C2<=14,"Add","") determines if its <= 14 days, if it is, then the word "Add" will be put in, otherwise it will be a blank.
A B C D E
View attachment 49085
my data starts in Column A; I would also make the DataRange a filtered range so I could filter on Column D- if it equals 'Add', then sum up amounts.

 

[etaf]

the SUMIF() would be used
=SUMIF(B1:B5,">="&TODAY()-14,A1:A5)

BUT not sure I understand, I'm using the function today() and then subtracting 14 days - so it will be dynamic and based on the date the spreadsheet is open

This needs to be dynamic because whenever I run the report, today's date will be the day that I compare the current month to vs the prior month (based on the number of days in the month today is).

Round.xlsm
	A	B	C	D
1	7	9/1/21
2	3	9/2/21		11
3	20	9/23/21
4	5	10/3/21
5	6	10/5/21
Sheet1

Cell Formulas
Range	Formula
D2	D2	=SUMIF(B1:B5,">="&TODAY()-14,A1:A5)

 

[steve the fish]

If im reading correctly:

=SUMIFS(A:A,B:B,">="&EOMONTH(TODAY(),-1)+1)

and

=SUMIFS(A:A,B:B,">="&EOMONTH(TODAY(),-2)+1,B:B,"<"&EDATE(TODAY(),-1)+1)

 

[BrettOlbrys1]

I came up with this formula to get me the same number of days at the same time last month:

=(EOMONTH(TODAY(),-2)+1)+((TODAY()-EOMONTH(TODAY(),-1)+1)-2)

But this will fail when/if I run the report during March on the 29th, 30, or 31st because February doesn't have those days OR if I run it on the 31st of a month and the preceding month only has 30 days.

 

[psycoperl]

I am thinking that you may need to have two hidden columns "C" Month Code which uses the formula MONTH(B2) where B2 = the date. Then Column D which uses a formula =DAY(B2)<=DAY(NOW()) to get the day of the month from both the month in column B and today's day of the month and determines if it is earlier or later than today's day of month.

For Last Month I used the formula =SUMIFS($A$2:$A$2000,$C$2:$C$2000,MONTH(NOW())-1,$D$2:$D$2000,TRUE)
For this Month I used the formula =SUMIFS($A$2:$A$2000,$C$2:$C$2000,MONTH(NOW()),$D$2:$D$2000,TRUE)

Basically it is saying - Sum A2 to A2000 where Column C=Month and D=True.

OptOut_Stats_Chart.xlsx
	A	B	C	D	E	F	G	H
1	Items	Date	MONTH CODE	DAY OF MONTH <= TODAY
2	7	9/1/21	9	TRUE
3	3	9/2/21	9	TRUE
4	20	9/23/21	9	FALSE
5	5	10/3/21	10	TRUE			Last Month	10
6	6	10/5/21	10	TRUE			This Month	11
7			1	TRUE
8			1	TRUE
Sheet2

Cell Formulas
Range	Formula
C2:C8	C2	=MONTH(B2)
D2:D8	D2	=DAY(B2)<=DAY(NOW())
H5	H5	=SUMIFS($A$2:$A$2000,$C$2:$C$2000,MONTH(NOW())-1,$D$2:$D$2000,TRUE)
H6	H6	=SUMIFS($A$2:$A$2000,$C$2:$C$2000,MONTH(NOW()),$D$2:$D$2000,TRUE)

 

[steve the fish]

Ive given you one. Take it from the reply you havent tried it? EDATE will happily deal with the 29th March producing 28th February (unless a leap year).