sum with multiple conditions

[aarti_rto]

Hello Experts.

i have below data..

Date

01-01-2024​

01-01-2024​

01-01-2024​

01-01-2024​

01-01-2024​

01-01-2024​

03-01-2024​

03-01-2024​

05-01-2024​

05-01-2024​

06-01-2024​

06-01-2024​

07-01-2024​

07-01-2024​

08-01-2024​

08-01-2024​

09-01-2024​

09-01-2024​

10-01-2024​

10-01-2024​

11-01-2024​

11-01-2024​

12-01-2024​

Part Number

Opening

Receipt

Phy issue

SAP issue

Closing

Diff

Phy issue

SAP issue

Phy issue

SAP issue

Phy issue

SAP issue

Phy issue

SAP issue

Phy issue

SAP issue

Phy issue

SAP issue

Phy issue

SAP issue

Phy issue

SAP issue

Phy issue

A

100​

958​

182​

674​

609​

459​

996​

889​

697​

521​

323​

234​

363​

608​

805​

743​

429​

809​

417​

223​

B

100​

925​

928​

486​

315​

633​

246​

870​

693​

911​

503​

456​

783​

642​

810​

869​

292​

356​

683​

174​

C

100​

763​

368​

428​

717​

785​

821​

930​

516​

372​

692​

243​

947​

105​

237​

826​

665​

679​

622​

253​

and need below output in a separate sheet

Week Num	1	2	3
Part Number	Total Phy issue	Total SAP issue	Total Phy issue	Total SAP issue	Total Phy issue	Total SAP issue
A
B
C

 

[awoohaw]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Date	2024-01-01	2024-01-01	2024-01-01	2024-01-01	2024-01-01	2024-01-01	2024-01-03	2024-01-03	2024-01-05	2024-01-05	2024-01-06	2024-01-06	2024-01-07	2024-01-07	2024-01-08	2024-01-08	2024-01-09	2024-01-09	2024-01-10	2024-01-10	2024-01-11	2024-01-11	2024-01-12
2	Part Number	Opening	Receipt	Phy issue	SAP issue	Closing	Diff	Phy issue	SAP issue	Phy issue	SAP issue	Phy issue	SAP issue	Phy issue	SAP issue	Phy issue	SAP issue	Phy issue	SAP issue	Phy issue	SAP issue	Phy issue	SAP issue	Phy issue
3	A	100	0	958	182	0	0	674	609	459	996	889	697	521	323	234	363	608	805	743	429	809	417	223
4	B	100	0	925	928	0	0	486	315	633	246	870	693	911	503	456	783	642	810	869	292	356	683	174
5	C	100	0	763	368	0	0	428	717	785	821	930	516	372	692	243	947	105	237	826	665	679	622	253
6	and need below output in a separate sheet
7		1	1	1	1	1	1	1	1	1	1	1	1	2	2	2	2	2	2	2	2	2	2	2
8	Week Num	1	2	3
9	Part Number	Total Phy issue	Total SAP issue	Total Phy issue	Total SAP issue	Total Phy issue	Total SAP issue
10	A	2980	2337	0
11	B	2914	3071	0
12	C	2906	3163	0
Sheet1

Cell Formulas
Range	Formula
B7:X7	B7	=WEEKNUM(B1)
B10:D12	B10	=SUMPRODUCT((SUBSTITUTE(B$9,"Total ","")=($B$2:$X$2))*(($A10=$A$3:$A$5))*($B$3:$X$5)*(WEEKNUM(1*$B$1:$X$1)=B$8))

 

[aarti_rto]

thank you , can you explain how this is verifying column heading?

 

[awoohaw]

It is array math with True and False multiplication:

(I"m sorry the column formatting is all goofy in the xl2bb mini sheet below, I can't figure out how to keep merged cells aligned with the non merged cells).
If you paste this into your workbook, i hope you can read it better. When looking here, you need to scroll to right to see the formula results.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Date	2024-01-01	2024-01-01	2024-01-01	2024-01-01	2024-01-01	2024-01-01	2024-01-03	2024-01-03	2024-01-05	2024-01-05	2024-01-06	2024-01-06	2024-01-07	2024-01-07	2024-01-08	2024-01-08	2024-01-09	2024-01-09	2024-01-10	2024-01-10	2024-01-11	2024-01-11	2024-01-12
2	Part Number	Opening	Receipt	Phy issue	SAP issue	Closing	Diff	Phy issue	SAP issue	Phy issue	SAP issue	Phy issue	SAP issue	Phy issue	SAP issue	Phy issue	SAP issue	Phy issue	SAP issue	Phy issue	SAP issue	Phy issue	SAP issue	Phy issue
3	A	100	0	958	182	0	0	674	609	459	996	889	697	521	323	234	363	608	805	743	429	809	417	223
4	B	100	0	925	928	0	0	486	315	633	246	870	693	911	503	456	783	642	810	869	292	356	683	174
5	C	100	0	763	368	0	0	428	717	785	821	930	516	372	692	243	947	105	237	826	665	679	622	253
6
7		1	1	1	1	1	1	1	1	1	1	1	1	2	2	2	2	2	2	2	2	2	2	2
8	Week Num	1	2	3
9	Part Number	Total Phy issue	Total SAP issue	Total Phy issue	Total SAP issue	Total Phy issue	Total SAP issue
10	A	2980	2337	0
11	B	2914	3071	0
12	C	2906	3163	0
13
14		Breaking down the formula in cell B10:
15
16		This first part strips out the "Total " of the output column header description, and finds matches in the data header. This give you all the columns that match the column header description.
17		=(SUBSTITUTE(B$9,"Total ","")=($B$2:$X$2))
18		FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE
19
20		This first part strips out the "Total " of the output column header description, and finds matches in the data header. This give you all the columns that match the column header description.
21		=(($A10=$A$3:$A$5))*($B$3:$X$5)
22		100	0	958	182	0	0	674	609	459	996	889	697	521	323	234	363	608	805	743	429	809	417	223
23		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
24		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
25
26		This first part strips out the "Total " of the output column header description, and finds matches in the data header. This give you all the columns that match the column header description.
27		=(WEEKNUM(1*$B$1:$X$1)=B$8)
28		TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
29
30		This first part strips out the "Total " of the output column header description, and finds matches in the data header. This give you all the columns that match the column header description.
31		=((SUBSTITUTE(B$9,"Total ","")=($B$2:$X$2))*(($A10=$A$3:$A$5))*($B$3:$X$5)*(WEEKNUM(1*$B$1:$X$1)=B$8))
32		0	0	958	0	0	0	674	0	459	0	889	0	0	0	0	0	0	0	0	0	0	0	0
33		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
34		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
35
36
37
38		SUMPRODUCT function puts it all together.
39
40		=((SUBSTITUTE(B$9,"Total ","")=($B$2:$X$2))*(($A10=$A$3:$A$5))*($B$3:$X$5)*(WEEKNUM(1*$B$1:$X$1)=B$8))
Sheet1

Cell Formulas
Range	Formula
B7:X7	B7	=WEEKNUM(B1)
B10:D12	B10	=SUMPRODUCT((SUBSTITUTE(B$9,"Total ","")=($B$2:$X$2))*(($A10=$A$3:$A$5))*($B$3:$X$5)*(WEEKNUM(1*$B$1:$X$1)=B$8))
B18:X18	B18	=(SUBSTITUTE(B$9,"Total ","")=($B$2:$X$2))
B22:X24	B22	=(($A10=$A$3:$A$5))*($B$3:$X$5)
B28:X28	B28	=(WEEKNUM(1*$B$1:$X$1)=B$8)
B32:X34	B32	=((SUBSTITUTE(B$9,"Total ","")=($B$2:$X$2))*(($A10=$A$3:$A$5))*($B$3:$X$5)*(WEEKNUM(1*$B$1:$X$1)=B$8))
Dynamic array formulas.