How to SUMIF based on criteria from another sheet?

[concavebuoyancy]

Hi all,

I have where at the start of every month, I import data where it includes the prices of fruit people have bought then, example:

Buyer (Start of month)	Fruit (Start of month)	Price (Start of month)
Person 1	Apple	$2
Person 1	Orange	$1
Person 1	Pear	$1
Person 2	Apple	$1
Person 2	Orange	$1
Person 2	Pear	$1

In the above data, Person 1 has bought $4 of fruit, and Person 2 $3 of fruit.

Next, I want to define which of these are 'expensive fruits', in that the price of the fruit they bought is 40% >= of the total purchase cost. In this case, only Person 1 has bought an expensive fruit (Apple), as it was 50% of their total purchase cost ($2 out of $4)

In another sheet at the end of every month, I import another set of data where it again includes the prices of fruit people have bought then, example:

Buyer (End of month)	Fruit (End of month)	Price (End of month)
Person 1	Apple	$3
Person 1	Orange	$2
Person 2	Apple	$2

This is where I am stuck - Based on these 2 sheets of data, I want to sum per person, how much they spent on fruit at the end of month, divided into expensive and non-expensive fruits.

Therefore, the results would look like:

Buyer	Total Cost (Non-expensive fruit)	Total Cost (Expensive fruit)
Person 1	$2 (The orange)	$3 (The apple, as it was >= 40% of their total purchase cost at the start of the month)
Person 2	$2 (The apple. This is not an expensive fruit for them, as it only cost them $1/33% of their total purchase cost at the start of the month)	0

 

[StephenCrump]

Welcome to the Forum!

It's not totally clear how you want to show the results. I've put them into one formula per line, but you can break this up into components if you like.

	A	B	C	D	E	F	G	H	I
1	Buyer (Start of month)	Fruit (Start of month)	Price (Start of month)
2	Person 1	Apple	$2
3	Person 1	Orange	$1
4	Person 1	Pear	$1
5	Person 2	Apple	$1
6	Person 2	Orange	$1
7	Person 2	Pear	$1
8						Expensive		Not expensive
9					Person 1	Apple,Orange	$5	-	$0
10					Person 2	Apple	$2	Banana	$1
11					Person 3	Orange	$1	-	$0
12	Buyer (End of month)	Fruit (End of month)	Price (End of month)
13	Person 1	Apple	$3
14	Person 1	Orange	$2
15	Person 2	Banana	$1
16	Person 2	Apple	$2
17	Person 3	Orange	$1
18
Sheet1

Cell Formulas
Range	Formula
E9:E11	E9	=UNIQUE(A13:A17)
F9:I11	F9	=LET(p,A$13:A$17=E9,X,C$13:C$17>=40%*SUMIFS(C$2:C$7,A$2:A$7,A$13:A$17),CHOOSE({1,2,3,4},TEXTJOIN(",",,FILTER(B$13:B$17,p*X,"-")),SUM(FILTER(C$13:C$17,p*X,0)),TEXTJOIN(",",,FILTER(B$13:B$17,p*NOT(X),"-")),SUM(FILTER(C$13:C$17,p*NOT(X),0))))
Dynamic array formulas.

 

[concavebuoyancy]

Welcome to the Forum!

It's not totally clear how you want to show the results. I've put them into one formula per line, but you can break this up into components if you like.

	A	B	C	D	E	F	G	H	I
1	Buyer (Start of month)	Fruit (Start of month)	Price (Start of month)
2	Person 1	Apple	$2
3	Person 1	Orange	$1
4	Person 1	Pear	$1
5	Person 2	Apple	$1
6	Person 2	Orange	$1
7	Person 2	Pear	$1
8						Expensive		Not expensive
9					Person 1	Apple,Orange	$5	-	$0
10					Person 2	Apple	$2	Banana	$1
11					Person 3	Orange	$1	-	$0
12	Buyer (End of month)	Fruit (End of month)	Price (End of month)
13	Person 1	Apple	$3
14	Person 1	Orange	$2
15	Person 2	Banana	$1
16	Person 2	Apple	$2
17	Person 3	Orange	$1
18
Sheet1

Cell Formulas
Range	Formula
E9:E11	E9	=UNIQUE(A13:A17)
F9:I11	F9	=LET(p,A$13:A$17=E9,X,C$13:C$17>=40%*SUMIFS(C$2:C$7,A$2:A$7,A$13:A$17),CHOOSE({1,2,3,4},TEXTJOIN(",",,FILTER(B$13:B$17,p*X,"-")),SUM(FILTER(C$13:C$17,p*X,0)),TEXTJOIN(",",,FILTER(B$13:B$17,p*NOT(X),"-")),SUM(FILTER(C$13:C$17,p*NOT(X),0))))
Dynamic array formulas.

Hi Stephen,

The results should look like this:

Buyer	Total Cost (Non-expensive fruit)	Total Cost (Expensive fruit)
Person 1	$2	$3
Person 2	$2	0

For Person 1: The $2 non-expensive fruit is the orange, which is non-expensive as it was bought for $1 originally, and not more than or equal to 40% of it's original purchase ($1/$4)
The $3 expensive fruit is the apple as it was bought for $2 originally, and not more than or equal to 40% of it's original purchase ($2/$4)

For Person 2: The $2 non-expensive fruit is the orange, which is non-expensive as it was bought for $1 originally, and not more than or equal to 40% of it's original purchase ($1/$3)

 

[Cubist]

With a helper column D, try

Book1
	A	B	C	D	E	F	G
1	Buyer (Start of month)	Fruit (Start of month)	Price (Start of month)	Helper
2	Person 1	Apple	2	Expensive
3	Person 1	Orange	1	Not expensive
4	Person 1	Pear	1	Not expensive
5	Person 2	Apple	1	Not expensive
6	Person 2	Orange	1	Not expensive
7	Person 2	Pear	1	Not expensive
8						Not expensive	Expensive
9					Person 1	2	3
10					Person 2	5	0
11					Person 3	0	0
12	Buyer (End of month)	Fruit (End of month)	Price (End of month)
13	Person 1	Apple	3
14	Person 1	Orange	2
15	Person 2	Banana	1
16	Person 2	Apple	2
17	Person 3	Orange	1
Sheet5

Cell Formulas
Range	Formula
D2:D7	D2	=IF(C2/SUM(FILTER($C$2:$C$7,$A$2:$A$7=A2))>40%,"Expensive", "Not expensive")
E9:E11	E9	=UNIQUE(A13:A17)
F9:F11	F9	=SUM(XLOOKUP(FILTER($B$2:$B$7,($D$2:$D$7=F$8)*($A$2:$A$7=E9),""),$B$13:$B$17,$C$13:$C$17,0))
G9:G11	G9	=SUM(XLOOKUP(FILTER($B$2:$B$7,($D$2:$D$7=G$8)*($A$2:$A$7=E9),""),$B$13:$B$17,$C$13:$C$17,0))
Dynamic array formulas.

 

[Cubist]

Without the helper column,

Book1
	A	B	C	D	E	F	G
1	Buyer (Start of month)	Fruit (Start of month)	Price (Start of month)
2	Person 1	Apple	2
3	Person 1	Orange	1
4	Person 1	Pear	1
5	Person 2	Apple	1
6	Person 2	Orange	1
7	Person 2	Pear	1
8						Not expensive	Expensive
9					Person 1	2	3
10					Person 2	5	0
11					Person 3	0	0
12	Buyer (End of month)	Fruit (End of month)	Price (End of month)
13	Person 1	Apple	3
14	Person 1	Orange	2
15	Person 2	Banana	1
16	Person 2	Apple	2
17	Person 3	Orange	1
Sheet5

Cell Formulas
Range	Formula
E9:E11	E9	=UNIQUE(A13:A17)
F9:F11	F9	=SUM(XLOOKUP(FILTER($B$2:$B$7,($C$2:$C$7/BYROW($A$2:$A$7,LAMBDA(p,SUM(FILTER($C$2:$C$7,$A$2:$A$7=p))))<=40%)*($A$2:$A$7=E9),""),$B$13:$B$17,$C$13:$C$17,0))
G9:G11	G9	=SUM(XLOOKUP(FILTER($B$2:$B$7,($C$2:$C$7/BYROW($A$2:$A$7,LAMBDA(p,SUM(FILTER($C$2:$C$7,$A$2:$A$7=p))))>40%)*($A$2:$A$7=E9),""),$B$13:$B$17,$C$13:$C$17,0))
Dynamic array formulas.

 

[StephenCrump]

The results should look like this ....

... so the end of month price does not impact the expensive/not expensive question.

Without the helper column ...

I was thinking along similar lines, with a BYROW construction. But try changing your C13 to $99, say, and see that that does to your results.

Here's my take:

	A	B	C	D	E	F	G
1	Buyer (Start of month)	Fruit (Start of month)	Price (Start of month)
2	Person 1	Apple	$2
3	Person 1	Orange	$1
4	Person 1	Pear	$1
5	Person 2	Apple	$1
6	Person 2	Orange	$1
7	Person 2	Pear	$1
8						Expensive	Not expensive
9					Person 1	$3	$2
10					Person 2	$0	$7
11					Person 3	$0	$1
12	Buyer (End of month)	Fruit (End of month)	Price (End of month)
13	Person 1	Apple	$3
14	Person 1	Orange	$2
15	Person 2	Banana	$1
16	Person 2	Apple	$2
17	Person 2	Pear	$4
18	Person 3	Apple	$1
Sheet1

Cell Formulas
Range	Formula
E9:E11	E9	=UNIQUE(A13:A18)
F9:F11	F9	=SUMPRODUCT((A$13:A$18=E9)*C$13:C$18*BYROW(A$13:B$18,LAMBDA(r,XLOOKUP(1,(TAKE(r,,1)=A$2:A$7)*(TAKE(r,,-1)=B$2:B$7),C$2:C$7>=40%*SUMIFS(C$2:C$7,A$2:A$7,A$2:A$7),0))))
G9:G11	G9	=SUMIFS(C$13:C$18,A$13:A$18,E9)-F9
Dynamic array formulas.

If a person and/or fruit is not found, I have defaulted to not expensive.

 

[Cubist]

I was thinking along similar lines, with a BYROW construction. But try changing your C13 to $99, say, and see that that does to your results.

I didn't consider the Person in the end-of-month report. I've updated my formula and it seems to match yours but the thread is getting long. I'll let the OP to have a chance to response.