Long IF Formula Calculation Trouble

[jarett]

I had noticed an inconsistency in my formula a week or two ago and I thought I corrected it but, I am noticing my output to the formula is incorrect again for some instances. The rows in question are row12-25, the total should be "12" not "13" if all the logic is correct, I don't know if the formula is counting a 0 on row 15 or why I am getting a "13" result. All other calculations for rows are correct. any issue seen in the formula?

Main_WIP.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	SalesOrderNo	ItemCode	QuantityOrdered	QuantityBackordered	UDF_QTY_PULLED	UDF_QTY_RECEIVED	UDF_PROCESSED_IC	UDF_PROCESS_INCOMPLETE	UDF_ALLOCATED	WarehouseCode	UDF_DATE_ALLOCATED	OH Addition
2	8492424	112-CBLB-A	1	1	0	1	N	N	Y	000	7/29/2024 0:00
3	8498813	112-CBLB-A	1	1	0	1	Y	N	N	000		2
4	8498058	112-CBLK-A	1	1	0	0	N	N	N	000		0
5	8491574	112-CBLW-A	1	1	0	1	N	N	N	000
6	8500770	112-CBLW-A	1	1	0	1	N	N	N	000		2
7	0026473	112-CCBI-A	4	4	0	4	Y	N	N	000		4
8	0024765	112-CHB-A	5	0	5	0	N	N	N	000
9	0026276	112-CHB-A	144	76	68	0	N	N	N	000
10	0026410	112-CHB-A	36	0	36	0	Y	N	N	000
11	0027042	112-CHB-A	1	0	1	0	N	N	Y	000	7/30/2024 0:00	37
12	0025756	112-CHCBL-A	1	1	0	1	N	N	Y	000	6/28/2024 0:00
13	8489735	112-CHCBL-A	1	1	0	1	N	N	N	000
14	8499180	112-CHCBL-A	1	1	0	1	N	N	Y	000	8/1/2024 0:00
15	8500792	112-CHCBL-A	0	0	0	0	N	N	Y	000	8/7/2024 0:00
16	8500876	112-CHCBL-A	1	1	0	1	N	N	N	000
17	8501125	112-CHCBL-A	1	1	0	1	N	N	Y	000	8/7/2024 0:00
18	8501400	112-CHCBL-A	1	1	0	1	N	N	Y	000	8/6/2024 0:00
19	8501846	112-CHCBL-A	1	1	0	1	N	N	Y	000	8/6/2024 0:00
20	8501895	112-CHCBL-A	1	1	0	1	N	N	Y	000	8/6/2024 0:00
21	8502328	112-CHCBL-A	1	1	0	1	N	N	N	000
22	8502581	112-CHCBL-A	1	1	0	1	N	N	Y	000	8/7/2024 0:00
23	8502703	112-CHCBL-A	1	1	0	1	N	Y	Y	000	8/7/2024 0:00
24	8502729	112-CHCBL-A	1	1	0	1	N	N	Y	000	8/7/2024 0:00
25	8503482	112-CHCBL-A	1	1	0	0	N	N	N	000		13
26	8489735	112-CHN-A	1	1	0	0	N	N	N	000
27	8502648	112-CHN-A	3	3	0	0	N	N	N	000		0
28	0027695	112-CHNBL-A	12	12	0	0	N	N	N	000		0
InventoryCount

Cell Formulas
Range	Formula
L2:L28	L2	=IF(B2<>B3,SUMIF(B$2:B2,B2,F$2:F2)+SUMIFS(E$2:E2,K$2:K2,"<>"&TODAY(),I$2:I2,"Y",B$2:B2,B2)+SUMIFS(E$2:E2,G$2:G2,"Y",B$2:B2,B2,I$2:I2,"<>Y")+SUMIFS(E$2:E2,H$2:H2,"Y",B$2:B2,B2)+SUMIFS(F$2:F2,H$2:H2,"Y",B$2:B2,B2),"")

 

[Fluff]

The 1st sumifs will return 12 & the last returns 1 so the total of 13 is correct.

 

[jarett]

The 1st sumifs will return 12 & the last returns 1 so the total of 13 is correct.

So, if I need the last SUMIFS to equal 12 as well, I am not sure how I get that because I only have a max total of 12 in column E&F for the item code 112-CHCBL-A

 

[Alex Blakenburg]

Its easier to spot the issue if you use SumIfs all the way through.
The first SumIf, sums all of F, the last Sumif, sums F again if H = Y and hence double counts those.
Under what conditions did you want F summed ?

Note: I'm not sure your Sum of column E are mutually exclusive either.

=IF(B25<>B26,
  SUMIFS(F$2:F25,B$2:B25,B25)
  +SUMIFS(E$2:E25,K$2:K25,"<>"&TODAY(),I$2:I25,"Y",B$2:B25,B25)
  +SUMIFS(E$2:E25,G$2:G25,"Y",B$2:B25,B25,I$2:I25,"<>Y")
  +SUMIFS(E$2:E25,H$2:H25,"Y",B$2:B25,B25)
  +SUMIFS(F$2:F25,H$2:H25,"Y",B$2:B25,B25)
,"")

 

[jarett]

Its easier to spot the issue if you use SumIfs all the way through.
The first SumIf, sums all of F, the last Sumif, sums F again if H = Y and hence double counts those.
Under what conditions did you want F summed ?

Note: I'm not sure your Sum of column E are mutually exclusive either.

=IF(B25<>B26,
  SUMIFS(F$2:F25,B$2:B25,B25)
  +SUMIFS(E$2:E25,K$2:K25,"<>"&TODAY(),I$2:I25,"Y",B$2:B25,B25)
  +SUMIFS(E$2:E25,G$2:G25,"Y",B$2:B25,B25,I$2:I25,"<>Y")
  +SUMIFS(E$2:E25,H$2:H25,"Y",B$2:B25,B25)
  +SUMIFS(F$2:F25,H$2:H25,"Y",B$2:B25,B25)
,"")

F should be summed always, if it has a quantity it should be counted (even if negative). Column E should be counted if column G or H is = Y or if (Column I is = Y and Column K <> todays date). I removed the last SUMIFS and the lines that I was getting the incorrect calculation came out correct, I think with that last SUMIFS it was counting Row 23 twice like you said.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Alex Blakenburg]

Just be aware that while these 2 lines summing column E are mutually exclusive:

  +SUMIFS(E$2:E25,K$2:K25,"<>"&TODAY(),I$2:I25,"Y",B$2:B25,B25)
  +SUMIFS(E$2:E25,G$2:G25,"Y",B$2:B25,B25,I$2:I25,"<>Y")

This line is not and has the potential to double count:

  +SUMIFS(E$2:E25,H$2:H25,"Y",B$2:B25,B25)