SUMIFS? Other formula? Confusion working with date ranges and individual numerical days

[anpathea]

Hello!

I've been trying to work through this issue via Google but just can't figure it out.

In Column B there are numbers relating to the monthly bill due dates, but does not express full dates. In other words, it is only the DD from a MM/DD/YYYY date.

In Column C are the costs associated with those bills in dollar amounts.

In Column I there is a sequential list of full dates (e.g. 2/4/2022, 2/9/2022, 3/7/2022, etc.).

In Column J, I am attempting to create a formula that adds up all values in C that fall between two dates from I, using the numerical days from B.

For example, in J22, I want to pull costs from Column C for due dates > 2/4/2022 (I21) and <=2/18/2022 (I22), using the Column B due dates. The problem I'm running into is I cannot figure out how to get Excel to work between a single number that represents the DD part of the date and the actual dates.

I keep running into the same problems where I am able to make it work for dates where the DD range goes up (e.g. the dates in the previous line, > 4, <= 18), but it doesn't work when it goes from one month to the next because something like March 25 to April 9 tries to pull anything > 25 and <= 9, which obviously results in nothing. Every time I think I've found a workaround, it ends up being exactly the same thing, and I just don't think my Excel skills or my Google skills are up to the task without some help. What I need is a single formula that I can drag down Column J that will reliably work.

Let me know if I can clarify anything! It's super confusing to me, so it's difficult to explain.

 

[StephenCrump]

Welcome to the Forum!

The first thing to establish is what's actually in your DD cells.

Excel stores dates as numbers. Today (10 Feb 2022) is stored as 44,602. Tomorrow will be 44,603.

Range A2:A5 below all contains the same number 44,602. But is is displayed differently depending on the format chosen.

	A	B
1	Today	Format
2	Thu 10 Feb 2022	ddd d mmm yyyy
3	10	dd
4	02-10-22	mm-dd-yy
5	44,602	#,##0
qw

Cell Formulas
Range	Formula
A2:A5	A2	=TEXT(TODAY(),B2)

So if you have a DD cell that displays as 10, say, is the number in the cell actually 10? Or has it perhaps just been formatted to display as 10?

 

[anpathea]

Welcome to the Forum!

The first thing to establish is what's actually in your DD cells.

Excel stores dates as numbers. Today (10 Feb 2022) is stored as 44,602. Tomorrow will be 44,603.

Range A2:A5 below all contains the same number 44,602. But is is displayed differently depending on the format chosen.

	A	B
1	Today	Format
2	Thu 10 Feb 2022	ddd d mmm yyyy
3	10	dd
4	02-10-22	mm-dd-yy
5	44,602	#,##0
qw

Cell Formulas
Range	Formula
A2:A5	A2	=TEXT(TODAY(),B2)

So if you have a DD cell that displays as 10, say, is the number in the cell actually 10? Or has it perhaps just been formatted to display as 10?

Thanks for the quick response!

I did not even consider changing those cells to a different format, so they are just 'General.'

I'm guessing I can do something if I change them to a date format?

 

[StephenCrump]

... I am able to make it work for dates where the DD range goes up (e.g. the dates in the previous line, > 4, <= 18) ...

Sorry, I've probably led you astray with Post #2. It sounds like your due dates are days of the month, e.g. 10, 15.

Here's one way you can count the number of due days on and between two dates:

	A	B	C	D	E
1	Date1	Date2	DueDay		NoTimes
2	20 Jan 2022	3 Feb 2022	1		1
3	20 Jan 2022	3 Feb 2022	31		1
4	20 Jan 2022	3 Feb 2022	6		0
5	31 Jan 2022	28 Feb 2022	30		1
6	20 Jan 2022	23 Mar 2022	7		2
7	20 Jan 2022	6 Mar 2022	6		2
8	6 Jan 2022	6 Mar 2022	6		3
9	20 Dec 2022	4 Jan 2023	20		1
Sheet1

Cell Formulas
Range	Formula
E2:E9	E2	=IF(OR(A2>B2,C2<1,C2>31),"-",12*(YEAR(B2)-YEAR(A2))+MONTH(B2)-MONTH(A2)+(C2>=DAY(A2))-(MIN(DAY(EOMONTH(B2,0)),C2)>DAY(B2)))

Is this something you can adapt?

 

[anpathea]

Sorry, I've probably led you astray with Post #2. It sounds like your due dates are days of the month, e.g. 10, 15.

Here's one way you can count the number of due days on and between two dates:

	A	B	C	D	E
1	Date1	Date2	DueDay		NoTimes
2	20 Jan 2022	3 Feb 2022	1		1
3	20 Jan 2022	3 Feb 2022	31		1
4	20 Jan 2022	3 Feb 2022	6		0
5	31 Jan 2022	28 Feb 2022	30		1
6	20 Jan 2022	23 Mar 2022	7		2
7	20 Jan 2022	6 Mar 2022	6		2
8	6 Jan 2022	6 Mar 2022	6		3
9	20 Dec 2022	4 Jan 2023	20		1
Sheet1

Cell Formulas
Range	Formula
E2:E9	E2	=IF(OR(A2>B2,C2<1,C2>31),"-",12*(YEAR(B2)-YEAR(A2))+MONTH(B2)-MONTH(A2)+(C2>=DAY(A2))-(MIN(DAY(EOMONTH(B2,0)),C2)>DAY(B2)))

Is this something you can adapt?

This is very cool!

To be clear, this is counting the number of times the due date exists between the two dates, right? That seems to be the case, but row 5 is not working properly. Between 31 Jan and 28 Feb, there should be zero instances of the date “30,” but it is showing 1.

I definitely like this idea though, I may play around with it later to see if I can figure it out, but if you know what’s happening that might be quicker!

Really appreciate the expertise!

 

[StephenCrump]

To be clear, this is counting the number of times the due date exists between the two dates, right? That seems to be the case, but row 5 is not working properly. Between 31 Jan and 28 Feb, there should be zero instances of the date “30,” but it is showing 1.

Yes, that's the intention.

If a bill falls due on the 31st of the month, my calculation assumes it will be paid on 31 Jan, 28/9 Feb, 31 March, 30 April .....

Please post back if you need further assistance. It's best if you can provide a screenshot using our XL2BB add-in, which you can download here: XL2BB - Excel Range to BBCode

 

[anpathea]

Yes, that's the intention.

If a bill falls due on the 31st of the month, my calculation assumes it will be paid on 31 Jan, 28/9 Feb, 31 March, 30 April .....

Please post back if you need further assistance. It's best if you can provide a screenshot using our XL2BB add-in, which you can download here: XL2BB - Excel Range to BBCode

Oh, of course! That makes perfect sense.

I’ll try implementing this later and see how it goes! Thanks again!

 

[bebo021999]

One more option 28 Feb not be counted with 29/30/31:

=SUMPRODUCT(--(DAY(ROW(INDIRECT(A2&":"&B2)))=C2))

 

[anpathea]

I figured it out!

This is long because I’m recapping everything because I went a different route, and because I don’t think the question I posed initially is as clear as I can present it here.

I genuinely appreciate the help you all provided, even though I did not ultimately use it in the solution. The guidance helped me think through the problem. I struggled to articulate my problem and needs initially, which made it more confusing than it had to be and led the responses astray a bit. That’s totally my fault, but also my inability to articulate everything was part of the reason I was running into the issues in the first place, so that makes sense.

If you’re curious about the solution, read on!

See the mini sheet below for an example of what my spreadsheet looks like starting out. “Manual Check” is literally just what the numbers should match (I noticed later there was an error in one of those, which is the whole reason I wanted this automated, aside from saving time).

Budget.xlsx
	A	B	C	D	E	F	G	H
1	Pay To	Due Date	Payment		Date 1	Date 2	Payment Due	Manual Check
2	Category 1				1/5/2022	1/19/2022		$2,871.62
3	Lorem	2	$45.13		1/19/2022	2/2/2022		$1,484.91
4	Ipsum	9	$120.50		2/2/2022	2/16/2022		$2,871.62
5	Category 2				2/16/2022	3/2/2022		$1,484.91
6	Dolor	7	$281.25		3/2/2022	3/16/2022		$2,871.62
7	Sit	13	$1,350.00		3/16/2022	3/30/2022		$589.68
8	Amet	22	$152.53		3/30/2022	4/13/2022		$2,657.09
9	Category 3				4/13/2022	4/27/2022		$1,262.29
10	Consectet	1	$75.25		4/27/2022	5/11/2022		$1,182.09
11	Abipsic	9	$10.11		5/11/2022	5/25/2022		$2,612.29
12	Elit	15	$134.43		5/25/2022	6/8/2022		$1,613.63
13	Donec	31	$125.00		6/8/2022	6/22/2022		$2,742.90
14	Category 4				6/22/2022	7/6/2022		$1,332.38
15	Guble	2	$649.85		7/6/2022	7/20/2022		$2,871.62
16	Rebo	15	$975.33		7/20/2022	8/3/2022		$1,484.91
17	Innil	29	$437.15		8/3/2022	8/17/2022		$2,871.62
Sheet1

Cell Formulas
Range	Formula
H2	H2	=C4+C6+C7+C11+C12+C16
H3	H3	=C3+C8+C10+C13+C15+C17
H4	H4	=C4+C6+C7+C11+C12+C16
H5	H5	=C3+C8+C10+C13+C15+C17
H6	H6	=C4+C6+C7+C11+C12+C16
H7	H7	=C8+C17
H8	H8	=C3+C4+C6+C7+C10+C11+C13+C15
H9	H9	=C8+C12+C16
H10	H10	=C3+C4+C6+C10+C11+C15
H11	H11	=C7+C8+C12+C16
H12	H12	=C3+C6+C10+C13+C15+C17
H13	H13	=C4+C7+C8+C11+C12+C16
H14	H14	=C3+C10+C13+C15+C17
H15	H15	=C4+C6+C7+C11+C12+C16
H16	H16	=C3++C8+C10+C13+C15+C17
H17	H17	=C4+C6+C7+C11+C12+C16

What I need is for each cell in G to sum the values of Payment (C:C) where Due Date (B:B) falls in the range > DAY(E) and <=(F).



My initial attempt, even before posing the question here, was using the following formula:



=SUMIFS(C:C,B:B,">"&DAY(E2),B:B,"<="&DAY(F2))



But this doesn’t work when DAY(E2) > DAY(F2):

Budget.xlsx
	E	F	G	H
1	Date 1	Date 2	Payment Due	Manual Check
2	1/5/2022	1/19/2022	2871.62	$2,871.62
3	1/19/2022	2/2/2022	0	$1,484.91
4	2/2/2022	2/16/2022	2871.62	$2,871.62
5	2/16/2022	3/2/2022	0	$1,484.91
6	3/2/2022	3/16/2022	2871.62	$2,871.62
7	3/16/2022	3/30/2022	589.68	$589.68
Sheet1

Cell Formulas
Range	Formula
G2:G7	G2	=SUMIFS(C:C,B:B,">"&DAY(E2),B:B,"<="&DAY(F2))
H2	H2	=C4+C6+C7+C11+C12+C16
H3	H3	=C3+C8+C10+C13+C15+C17
H4	H4	=C4+C6+C7+C11+C12+C16
H5	H5	=C3+C8+C10+C13+C15+C17
H6	H6	=C4+C6+C7+C11+C12+C16
H7	H7	=C8+C17

I understood what the problem was, but couldn’t figure out the solution, and this is when I posed my initial question. While working on it today, I decided to try to come up with a formula that works for the other situations, knowing it would not work for the prior situation, and came up with this:



=(SUMIFS(C:C,B:B,">"&DAY(E3),B:B,"<32"))+(SUMIFS(C:C,B:B,"<="&DAY(F3),B:B,">0"))

I don’t know if these are the best formula for either situation, but I know they work:

Budget.xlsx
	E	F	G	H
1	Date 1	Date 2	Payment Due	Manual Check
2	1/5/2022	1/19/2022	2871.62	$2,871.62
3	1/19/2022	2/2/2022	1484.91	$1,484.91
4	2/2/2022	2/16/2022	2871.62	$2,871.62
5	2/16/2022	3/2/2022	1484.91	$1,484.91
6	3/2/2022	3/16/2022	2871.62	$2,871.62
7	3/16/2022	3/30/2022	589.68	$589.68
8	3/30/2022	4/13/2022	2657.09	$2,657.09
Sheet1

Cell Formulas
Range	Formula
G2,G4,G6:G7	G2	=SUMIFS(C:C,B:B,">"&DAY(E2),B:B,"<="&DAY(F2))
H2	H2	=C4+C6+C7+C11+C12+C16
G3,G5,G8	G3	=(SUMIFS(C:C,B:B,">"&DAY(E3),B:B,"<32"))+(SUMIFS(C:C,B:B,"<="&DAY(F3),B:B,">0"))
H3	H3	=C3+C8+C10+C13+C15+C17
H4	H4	=C4+C6+C7+C11+C12+C16
H5	H5	=C3+C8+C10+C13+C15+C17
H6	H6	=C4+C6+C7+C11+C12+C16
H7	H7	=C8+C17
H8	H8	=C3+C4+C6+C7+C10+C11+C13+C15

I figured an IF formula would work here. I ended up hitting a bunch of snags along the way with various syntax issues and because I was being careless when combining them (I copied and pasted the working formulas to make sure they worked, but since I made the second formula using different reference cells, the combined formula tried to use four different cells instead of two). Anyway, I the following is the working formula:

=IF(DAY(E2)<DAY(F2),(SUMIFS(C:C,B:B,">"&DAY(E2),B:B,"<="&DAY(F2))),(SUMIFS(C:C,B:B,">"&DAY(E2),B:B,"<32"))+(SUMIFS(C:C,B:B,"<="&DAY(F2),B:B,">0")))

You can see the functioning, solved spreadsheet here:

Budget.xlsx
	A	B	C	D	E	F	G	H
1	Pay To	Due Date	Payment		Date 1	Date 2	Payment Due	Manual Check
2	Category 1				1/5/2022	1/19/2022	2871.62	$2,871.62
3	Lorem	2	$45.13		1/19/2022	2/2/2022	1484.91	$1,484.91
4	Ipsum	9	$120.50		2/2/2022	2/16/2022	2871.62	$2,871.62
5	Category 2				2/16/2022	3/2/2022	1484.91	$1,484.91
6	Dolor	7	$281.25		3/2/2022	3/16/2022	2871.62	$2,871.62
7	Sit	13	$1,350.00		3/16/2022	3/30/2022	589.68	$589.68
8	Amet	22	$152.53		3/30/2022	4/13/2022	2657.09	$2,657.09
9	Category 3				4/13/2022	4/27/2022	1262.29	$1,262.29
10	Consectet	1	$75.25		4/27/2022	5/11/2022	1744.24	$1,744.24
11	Abipsic	9	$10.11		5/11/2022	5/25/2022	2612.29	$2,612.29
12	Elit	15	$134.43		5/25/2022	6/8/2022	1613.63	$1,613.63
13	Donec	31	$125.00		6/8/2022	6/22/2022	2742.9	$2,742.90
14	Category 4				6/22/2022	7/6/2022	1332.38	$1,332.38
15	Guble	2	$649.85		7/6/2022	7/20/2022	2871.62	$2,871.62
16	Rebo	15	$975.33		7/20/2022	8/3/2022	1484.91	$1,484.91
17	Innil	29	$437.15		8/3/2022	8/17/2022	2871.62	$2,871.62
Sheet1

Cell Formulas
Range	Formula
G2:G17	G2	=IF(DAY(E2)<DAY(F2),(SUMIFS(C:C,B:B,">"&DAY(E2),B:B,"<="&DAY(F2))),(SUMIFS(C:C,B:B,">"&DAY(E2),B:B,"<32"))+(SUMIFS(C:C,B:B,"<="&DAY(F2),B:B,">0")))
H2	H2	=C4+C6+C7+C11+C12+C16
H3	H3	=C3+C8+C10+C13+C15+C17
H4	H4	=C4+C6+C7+C11+C12+C16
H5	H5	=C3+C8+C10+C13+C15+C17
H6	H6	=C4+C6+C7+C11+C12+C16
H7	H7	=C8+C17
H8	H8	=C3+C4+C6+C7+C10+C11+C13+C15
H9	H9	=C8+C12+C16
H10	H10	=C3+C4+C6+C10+C11+C15+C13+C17
H11	H11	=C7+C8+C12+C16
H12	H12	=C3+C6+C10+C13+C15+C17
H13	H13	=C4+C7+C8+C11+C12+C16
H14	H14	=C3+C10+C13+C15+C17
H15	H15	=C4+C6+C7+C11+C12+C16
H16	H16	=C3++C8+C10+C13+C15+C17
H17	H17	=C4+C6+C7+C11+C12+C16