Need help on Combining the SUMIFS and VLOOKUP Functions

[Dhinakaran]

Hello All,

Need your help on combining SUMIFS and VLOOKUP combo. 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	130327	174105	130878
4	Groceries
Summary

Cell Formulas
Range	Formula
B3:D3	B3	=SUMIFS(Fruits!$E$2:$E$11,Fruits!$B$2:$B$11,Summary!B2)

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

 

[Peter_SSs]

Is this what you mean?

Dhinakaran.xlsm
	A	B	C	D	E
1	Date	Type	Status	Vendor	Bill Value
2	11-Jul-23	WIP	Stock	ABC Inc	41002
3	16-Jul-23	Completed	Sold	Apple Exports	41249
4	21-Jul-23	Reversal	Returned	Murray LLP	41738
5	26-Jul-23	Completed	Sold	ABC Inc	44328
6	31-Jul-23	WIP	Stock	Apple Exports	44330
7	05-Aug-23	WIP	Stock	Murray LLP	44374
8	10-Aug-23	WIP	Stock	ABC Inc	44399
9	15-Aug-23	Reversal	Returned	Apple Exports	44533
10	20-Aug-23	Reversal	Returned	Murray LLP	44607
11	25-Aug-23	Completed	Sold	ABC Inc	44750
Fruits

Dhinakaran.xlsm
	A	B	C	D	E
1	Date	Type	Status	Vendor	Bill Value
2	11-Jul-23	WIP	Stock	ABC Inc	500957
3	16-Jul-23	Completed	Sold	Apple Exports	157255
4	21-Jul-23	Reversal	Returned	Murray LLP	125744
5	26-Jul-23	Completed	Sold	ABC Inc	113170
6	31-Jul-23	WIP	Stock	Apple Exports	59764
7	05-Aug-23	WIP	Stock	Murray LLP	54443
8	10-Aug-23	WIP	Stock	ABC Inc	50692
9	15-Aug-23	Reversal	Returned	Apple Exports	50538
10	20-Aug-23	Reversal	Returned	Murray LLP	50282
11	25-Aug-23	Completed	Sold	ABC Inc	50112
Groceries

Dhinakaran.xlsm
	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(f,Groceries!$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)))

 

[Dhinakaran]

Thanks Peter_SSs. Work fine in excel. However, I need to replicate the same in Google Sheets where the TAKE function is not supported in it, Please suggest.

 

[Peter_SSs]

Thanks Peter_SSs. Work fine in excel.

You're welcome. Thanks for the confirmation.

I need to replicate the same in Google Sheets where the TAKE function is not supported in it, Please suggest.

Google Sheets question should be asked in the forum nominated for that. I suggest that you start a thread there and provide a link to this one.