IF Statements For Totals or Pivots

[jarett]

I am pulling data from MS SQL and I have a few columns that I have to take logic into place in order to get an "add" quantity in a separate column for certain "item code" values. This is my logic that gets the counts, the two columns I need to total are K & L, udf_Qty received- always count if > 0
udf_Qty pulled - count if (allocated = Y and date allocated <> today) or processed incomplete = Y

The goal is to just get a total to display just once, so I am not sure if thats possible or if I would have to add a column to get the count of each row first then add a column after that to get the total? I attached a sample copy of file . The last column labeled "add" would what I want my results to be and displayed like this. Would a pivot work for this?

TEST.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	DATE ALLOCATED	add
2	8304444	10022328-BEIGE/KHAKI-LGR	5	0	5	0	N	N	Y	000
3	8307056	10022328-BEIGE/KHAKI-LGR	3	0	3	0	N	N	N	000
4	8309702	10022328-BEIGE/KHAKI-LGR	5	0	5	0	N	N	Y	000		10
5	8245405	10022328-BEIGE/KHAKI-MEDR	2	0	2	0		N	Y	000
6	8309710	10022328-BEIGE/KHAKI-MEDR	3	0	3	0	N	N	N	000		2
7	8267639	10022328-BEIGE/KHAKI-SMLR	2	0	2	0	N	N	N	000	6/16/2023
8	8291578	10022328-BEIGE/KHAKI-SMLR	2	1	1	1	N	N	Y	000		2
9	8287281	10022328-BEIGE/KHAKI-XLR	5	0	5	0	N	N	N	000
10	8300303	10022328-BEIGE/KHAKI-XLR	1	1	0	1	N	N	N	000
11	8307479	10022328-BEIGE/KHAKI-XLR	1	0	1	0	N	N	Y	000
12	8310457	10022328-BEIGE/KHAKI-XLR	5	0	5	0	N	N	Y	000		7
13	8245405	10022328-BEIGE/KHAKI-XXLR	5	0	5	0	N	N	Y	000
14	8309990	10022328-BEIGE/KHAKI-XXLR	4	0	4	0	N	N	N	000		5
15	8298394	10022328-BEIGE/KHAKI-XXLTR	3	0	3	0	Y	N	N	000		3
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G4	Cell Value	duplicates	text	NO

 

[jarett]

I've re-posted this on another forum to see if I can get any help from there.
IF Statements For Totals or Pivots

 

[Fluff]

I don't agree with your results, but is this what you want.

TEST.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	DATE ALLOCATED	add
2	8304444	10022328-BEIGE/KHAKI-LGR	5	0	5	0	N	N	Y	000
3	8307056	10022328-BEIGE/KHAKI-LGR	3	0	3	0	N	N	N	000
4	8309702	10022328-BEIGE/KHAKI-LGR	5	0	5	0	N	N	Y	000		10
5	8245405	10022328-BEIGE/KHAKI-MEDR	2	0	2	0		N	Y	000
6	8309710	10022328-BEIGE/KHAKI-MEDR	3	0	3	0	N	N	N	000		2
7	8267639	10022328-BEIGE/KHAKI-SMLR	2	0	2	0	N	N	N	000	16/06/2023
8	8291578	10022328-BEIGE/KHAKI-SMLR	2	1	1	1	N	N	Y	000		1
9	8287281	10022328-BEIGE/KHAKI-XLR	5	0	5	0	N	N	N	000
10	8300303	10022328-BEIGE/KHAKI-XLR	1	1	0	1	N	N	N	000
11	8307479	10022328-BEIGE/KHAKI-XLR	1	0	1	0	N	N	Y	000
12	8310457	10022328-BEIGE/KHAKI-XLR	5	0	5	0	N	N	Y	000		6
13	8245405	10022328-BEIGE/KHAKI-XXLR	5	0	5	0	N	N	Y	000
14	8309990	10022328-BEIGE/KHAKI-XXLR	4	0	4	0	N	N	N	000		5
15	8298394	10022328-BEIGE/KHAKI-XXLTR	3	0	3	0	Y	N	N	000		0
Sheet1

Cell Formulas
Range	Formula
L2:L15	L2	=IF(COUNTIFS(B$2:B2,B2)=COUNTIFS(B:B,B2),SUMIFS(E:E,B:B,B2,I:I,"Y",K:K,"<>"&TODAY(),H:H,"<>Y"),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G4	Cell Value	duplicates	text	NO

 

[jarett]

I don't agree with your results, but is this what you want.

TEST.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	DATE ALLOCATED	add
2	8304444	10022328-BEIGE/KHAKI-LGR	5	0	5	0	N	N	Y	000
3	8307056	10022328-BEIGE/KHAKI-LGR	3	0	3	0	N	N	N	000
4	8309702	10022328-BEIGE/KHAKI-LGR	5	0	5	0	N	N	Y	000		10
5	8245405	10022328-BEIGE/KHAKI-MEDR	2	0	2	0		N	Y	000
6	8309710	10022328-BEIGE/KHAKI-MEDR	3	0	3	0	N	N	N	000		2
7	8267639	10022328-BEIGE/KHAKI-SMLR	2	0	2	0	N	N	N	000	16/06/2023
8	8291578	10022328-BEIGE/KHAKI-SMLR	2	1	1	1	N	N	Y	000		1
9	8287281	10022328-BEIGE/KHAKI-XLR	5	0	5	0	N	N	N	000
10	8300303	10022328-BEIGE/KHAKI-XLR	1	1	0	1	N	N	N	000
11	8307479	10022328-BEIGE/KHAKI-XLR	1	0	1	0	N	N	Y	000
12	8310457	10022328-BEIGE/KHAKI-XLR	5	0	5	0	N	N	Y	000		6
13	8245405	10022328-BEIGE/KHAKI-XXLR	5	0	5	0	N	N	Y	000
14	8309990	10022328-BEIGE/KHAKI-XXLR	4	0	4	0	N	N	N	000		5
15	8298394	10022328-BEIGE/KHAKI-XXLTR	3	0	3	0	Y	N	N	000		0
Sheet1

Cell Formulas
Range	Formula
L2:L15	L2	=IF(COUNTIFS(B$2:B2,B2)=COUNTIFS(B:B,B2),SUMIFS(E:E,B:B,B2,I:I,"Y",K:K,"<>"&TODAY(),H:H,"<>Y"),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G4	Cell Value	duplicates	text	NO

Do you not agree because the results from the formula are not what I listed them to be?

 

[RoryA]

Perhaps this:

=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),"")

It assumes the data is sorted on column B.

 

[Fluff]

Do you not agree because the results from the formula are not what I listed them to be?

Yes, so I don't know if I've misunderstood, or if your results are wrong.

 

[jarett]

Perhaps this:

=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),"")

It assumes the data is sorted on column B.

It looks good however I found an inconsistency, sometimes we will have column G and column I both = Y, but I do not want to count it twice. I am thinking that is what happened here, highlighted in yellow.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	SalesOrderNo	ItemCode	QuantityOrdered	QuantityBackordered	UDF_QTY_PULLED	UDF_QTY_RECEIVED	UDF_PROCESSED_IC	UDF_PROCESS_INCOMPLETE	UDF_ALLOCATED	WarehouseCode	UDF_DATE_ALLOCATED	Column1	Should be
2	0002614	10012552-BLUE-3030	1	0	1	0	N	N	Y	000	4/19/2023 0:00	1
3	8246611	10012552-BLUE-3030	1	0	1	0	Y	N	Y	000	5/30/2023 0:00	3
4	8250253	10012552-BLUE-3030	2	0	2	0		N	Y	000	5/2/2023 0:00	5
5	8278029	10012552-BLUE-3030	2	0	2	0	N	N	N	000	#####################	5
6	8306707	10012552-BLUE-3030	4	0	4	0	N	N	N	000		5
7	8308327	10012552-BLUE-3030	2	0	2	0	N	N	Y	000	6/13/2023 0:00	7	6
8	8304444	10012552-BLUE-3032	13	0	13	0	N	N	Y	000	6/16/2023 0:00	13
9	8310685	10012552-BLUE-3032	10	0	10	0	N	N	Y	000	6/16/2023 0:00	23	23
10	0002214	10012552-BLUE-3034	-5	0	-5	0	N	N	N	000	#####################	0
11	8267603	10012552-BLUE-3034	2	0	2	0	N	N	Y	000	6/15/2023 0:00	2
12	8308793	10012552-BLUE-3034	1	0	1	0	N	N	N	000		2	2
13	8308892	10012552-BLUE-3036	1	0	1	0	N	N	N	000		0	0
Sheet1

 

[GuzmanServices]

If the only issue with RoryA's formula is that it returned a 3 instead of a 2, the following bolded addition to the formula on Cell L2 should resolve the issue of duplicate "Y" in Columns G & I - assuming data is sorted on Column B.
=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"),"")

 

[jarett]

If the only issue with RoryA's formula is that it returned a 3 instead of a 2, the following bolded addition to the formula on Cell L2 should resolve the issue of duplicate "Y" in Columns G & I - assuming data is sorted on Column B.
=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"),"")

That worked! Thanks to everyone!