Give back the most/least profitable product

[Amaden95]

Hey Guys/Girls, I need some help with getting back the most profitable product in a single cell and a least profitable in another. I had a working formula but it didn't worked with empty spaces.

I have a summary sheet and a second data sheet where i store all the information.

On the summary sheet all I need to see is the most and the least profitable.

Profitability.xlsx
	A	B
1		Name of product
2	Most profitable
3	Least profitable
Summary

on the second sheet let's call it "data" i have a column with different name of products and next to them the profit they generated, each a bit different.

Profitability.xlsx
	A	B	C
1	Name of product	Profit	Minus
2	a	10	0
3	b	20	0
4	a	0	20
5	a	100	0
6	b	0	0
7	c	0	30
8	c	0	20
9	c	50	0
10	d	10	0
11	b	30	0
12	b	0	0
13	c	0	60
14	a	0	10
Data

All I need back is the name of the product that made the most and least profit withouth giving an error when i extend it over hundreds of empty space.

If anyone got an answer please help.
Thanks.

 

[Snakehips]

@Amaden95 Welcome to MrExcel

Maybe try

MRXLMAY21.xlsm
	A	B
1		Product
2	Most	a
3	Least	c
Sheet3

Cell Formulas
Range	Formula
B2	B2	=INDEX(Data!$A:$A,MATCH(MAX(Data!B:B),Data!B:B,0))
B3	B3	=INDEX(Data!$A:$A,MATCH(MAX(Data!C:C),Data!C:C,0))

Hope that helps

 

[Amaden95]

@Amaden95 Welcome to MrExcel

Maybe try

MRXLMAY21.xlsm
	A	B
1		Product
2	Most	a
3	Least	c
Sheet3

Cell Formulas
Range	Formula
B2	B2	=INDEX(Data!$A:$A,MATCH(MAX(Data!B:B),Data!B:B,0))
B3	B3	=INDEX(Data!$A:$A,MATCH(MAX(Data!C:C),Data!C:C,0))

Hope that helps

Thanks for the help, but this is not exactly what im looking for.
If I'm not wrong, this formula gives back the name of the product which generated the highest single profit.
What 'm looking for is to sum every profit A generated and sum the profit B generated and C.. and so on separately and check which is the highest and the lowest overall.

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F	G
1	Name of product	Profit	Minus			Name of product
2	a	10	0		Most profitable	a	80
3	b	20	0		Least profitable	c	-60
4	a	0	20
5	a	100	0
6	b	0	0
7	c	0	30
8	c	0	20
9	c	50	0
10	d	10	0
11	b	30	0
12	b	0	0
13	c	0	60
14	a	0	10
Sheet3

Cell Formulas
Range	Formula
F2	F2	=INDEX(A$2:A$14,MATCH(MAX(SUMIF(A$2:A$14,A$2:A$14,B$2:B$14)-SUMIF(A$2:A$14,A$2:A$14,C$2:C$14)),SUMIF(A$2:A$14,A$2:A$14,B$2:B$14)-SUMIF(A$2:A$14,A$2:A$14,C$2:C$14),0))
G2:G3	G2	=SUMIF(A$2:A$14,F2,B$2:B$14)-SUMIF(A$2:A$14,F2,C$2:C$14)
F3	F3	=INDEX(A$2:A$14,MATCH(MIN(SUMIF(A$2:A$14,A$2:A$14,B$2:B$14)-SUMIF(A$2:A$14,A$2:A$14,C$2:C$14)),SUMIF(A$2:A$14,A$2:A$14,B$2:B$14)-SUMIF(A$2:A$14,A$2:A$14,C$2:C$14),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Amaden95]

How about:

Book1
	A	B	C	D	E	F	G
1	Name of product	Profit	Minus			Name of product
2	a	10	0		Most profitable	a	80
3	b	20	0		Least profitable	c	-60
4	a	0	20
5	a	100	0
6	b	0	0
7	c	0	30
8	c	0	20
9	c	50	0
10	d	10	0
11	b	30	0
12	b	0	0
13	c	0	60
14	a	0	10
Sheet3

Cell Formulas
Range	Formula
F2	F2	=INDEX(A$2:A$14,MATCH(MAX(SUMIF(A$2:A$14,A$2:A$14,B$2:B$14)-SUMIF(A$2:A$14,A$2:A$14,C$2:C$14)),SUMIF(A$2:A$14,A$2:A$14,B$2:B$14)-SUMIF(A$2:A$14,A$2:A$14,C$2:C$14),0))
G2:G3	G2	=SUMIF(A$2:A$14,F2,B$2:B$14)-SUMIF(A$2:A$14,F2,C$2:C$14)
F3	F3	=INDEX(A$2:A$14,MATCH(MIN(SUMIF(A$2:A$14,A$2:A$14,B$2:B$14)-SUMIF(A$2:A$14,A$2:A$14,C$2:C$14)),SUMIF(A$2:A$14,A$2:A$14,B$2:B$14)-SUMIF(A$2:A$14,A$2:A$14,C$2:C$14),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you Eric!
Works wonderfully.

 

[Eric W]

Glad we could help!