SUMIFS formula not working as expected

[ac7]

Hello -

I am struggling with a formula that I'm hoping someone can help with. Here is my current data (my apologies for not being able to use the XL2BB addin).

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1	Missing Promised	Today or Prior	1 to 30 Days	31 to 60 Days	Vendor	Item	Quantity	Promised	Expected
2	240​	0​	120​	160​	Vendor	12345	40		8/1/2023
3					Vendor	12345	40		8/1/2023
4					Vendor	12345	40	8/11/2023	8/11/2023
5					Vendor	12345	40		8/31/2023
6					Vendor	12345	40		9/1/2023
7					Vendor	12345	80		9/1/2023

Formula in A2:

=IF($F2=$F1,"",SUMIFS($G:$G,$F:$F,$F2,$H:$H,""))

Formula in B2:

=IF($F2=$F1,"",SUMIFS($G:$G,$F:$F,$F2,$H:$H,"<>",$I:$I,"<="&TODAY()))

Formula in C2:

=IF($F2=$F1,"",SUMIFS($G:$G,$F:$F,$F2,$H:$H,"<>",$I:$I,">"&TODAY(),$I:$I,"<="&TODAY()+30))

Formula in D2:

=IF($F2=$F1,"",SUMIFS($G:$G,$F:$F,$F2,$H:$H,"<>",$I:$I,">"&TODAY()+30,$I:$I,"<="&TODAY()+60))

The SUMIFS formula in A2 is working correctly. It sums only quantities with a missing promised date. The formulas in B2 through D2 are not working as I had expected. I only want these to sum for the listed date ranges if there is a promised date. If there is no promised date, then it should not sum. Here's what I'm expecting for results:

Any help would be greatly appreciated.

 

[BigDawg15]

*ac7,

Using the formulas you have listed, I am getting your expected results.

BigDawg15

 

[ac7]

I just tried it again and am still getting these results:

This is what I should be getting:

 

[DanteAmor]

The formulas are correct.

Check that the cells do not have blank spaces, that is, the cells in column H must be empty.

Delete cells H2, H3, H5, H6 and H7.

Also check that you have no data after row 7.

Dante Amor
	A	B	C	D	E	F	G	H	I
1	Missing Promised	Today or Prior	1 to 30 Days	31 to 60 Days	Vendor	Item	Quantity	Promised	Expected
2	240	0	40	0	Vendor	12345	40		01-ago
3					Vendor	12345	40		01-ago
4					Vendor	12345	40	11-ago	11-ago
5					Vendor	12345	40		31-ago
6					Vendor	12345	40		01-sep
7					Vendor	12345	80		01-sep
Hoja1

Cell Formulas
Range	Formula
A2	A2	=IF($F2=$F1,"",SUMIFS($G:$G,$F:$F,$F2,$H:$H,""))
B2	B2	=IF($F2=$F1,"",SUMIFS($G:$G,$F:$F,$F2,$H:$H,"<>",$I:$I,"<="&TODAY()))
C2	C2	=IF($F2=$F1,"",SUMIFS($G:$G,$F:$F,$F2,$H:$H,"<>",$I:$I,">"&TODAY(),$I:$I,"<="&TODAY()+30))
D2	D2	=IF($F2=$F1,"",SUMIFS($G:$G,$F:$F,$F2,$H:$H,"<>",$I:$I,">"&TODAY()+30,$I:$I,"<="&TODAY()+60))

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

 

[ac7]

Ah, ok. Thank you for the reply. Is there a way that I can automatically clear these out or adjust the formula to take this situation into account? I have a lot of lines that I'm hoping to not have to manually clear.

 

[DanteAmor]

But you should have your sheet clean of strange things. Otherwise you will have to adapt these formulas and any other formulas you use or any other situations that use that data.

I recommend you perform an autofilter on each of those columns and check what is not Empty and is not a number, then you delete those cells.