Need help on combining SUMIFS and VLOOKUP functions.

[Dhinakaran]

Hello All,

Need your help on combining SUMIFS and VLOOKUP combo in Google Sheets. I've been trying to figure our the formula and i'm missing it.

Below is the snap of the summary table which i've been trying to prepare summary of sales where i need to replicated the values for type of sales (Completed,WIP,Reversal) against its status (Stock,Sold,Returned).

Please help on the same, TIA.

Summary Sheet:

Sample.xlsx
	A	B	C	D	E	F	G	H	I	J
1		Stock			Sold			Returned
2		Completed	WIP	Reversal	Completed	WIP	Reversal	Completed	WIP	Reversal
3	Fruits	0	174105	0	130327	0	0	0	0	130878
4	Groceries	0	665856	0	320537	0	0	0	0	226564
Summary

Cell Formulas
Range	Formula
B3:J3	B3	=LET(f,Fruits!$A$2:$E$11,SUM(TAKE(FILTER(f,(INDEX(f,0,3)=LOOKUP("zzz",$B$1:B$1))*(INDEX(f,0,2)=B$2),0),,-1)))
B4:J4	B4	=LET(g,Groceries!$A$2:$E$11,SUM(TAKE(FILTER(g,(INDEX(g,0,3)=LOOKUP("zzz",$B$1:B$1))*(INDEX(g,0,2)=B$2),0),,-1)))

Fruits sheet:

Date	Type	Status	Vendor	Bill Value
11-Jul-23	WIP	Stock	ABC Inc	41002
16-Jul-23	Completed	Sold	Apple Exports	41249
21-Jul-23	Reversal	Returned	Murray LLP	41738
26-Jul-23	Completed	Sold	ABC Inc	44328
31-Jul-23	WIP	Stock	Apple Exports	44330
5-Aug-23	WIP	Stock	Murray LLP	44374
10-Aug-23	WIP	Stock	ABC Inc	44399
15-Aug-23	Reversal	Returned	Apple Exports	44533
20-Aug-23	Reversal	Returned	Murray LLP	44607
25-Aug-23	Completed	Sold	ABC Inc	44750

Groceries Sheet:

Date	Type	Status	Vendor	Bill Value
11-Jul-23	WIP	Stock	ABC Inc	500957
16-Jul-23	Completed	Sold	Apple Exports	157255
21-Jul-23	Reversal	Returned	Murray LLP	125744
26-Jul-23	Completed	Sold	ABC Inc	113170
31-Jul-23	WIP	Stock	Apple Exports	59764
5-Aug-23	WIP	Stock	Murray LLP	54443
10-Aug-23	WIP	Stock	ABC Inc	50692
15-Aug-23	Reversal	Returned	Apple Exports	50538
20-Aug-23	Reversal	Returned	Murray LLP	50282
25-Aug-23	Completed	Sold	ABC Inc	50112

 

[Dhinakaran]

Hi All,

Please share any suggestions possible on this post.