Count number of max values per unique value and column heading?

[kim2109]

This is a bit complicated to explain but I'll try my best.

I have column A with unique references per order. I have a formula to count how many unique values are in the column which is working well.
The next column (B) is a list of categories.
The next three columns are sales per category, with the headings of these columns corresponding to the categories listed in column B.

I need a formula to count the number of values in each category column, per each unique value in column A. I can do this!
The part I cannot do is, if a value in column A has two different categories in B, and then two separate values in M and N, I need it to only count which is highest in the total at the bottom.

I am stumped. I'm not averse to a helper column!

 

[RobP]

Hi,

when you say "and then two separate values in M and N" - do we need to know whats in Col M and N ?
thanks
Rob

 

[kim2109]

Hi Rob,

Sorry, that should be C and D (and E and F)

 

[RobP]

Hi Kim,

I could only offer a solution by using 2 Helper columns as such :

Hope it helps

Rob

count_no_of_max_values.xlsx
	A	B	C	D	E	F	G	H
1	Unique Ref	Category	Depot	Wholesaler	Multiples	Bulk	Totals
2	a	DEPOT	1701	0	0	0	1701	1
3	b	BULK	0	0	0	1	1	1
4		NOT ON LIST	0	0	0	0	0	#N/A
5	d	WHOLESALER	0	1482	0	0	1482	1
6	d	WHOLESALER	0	0	0	0	0	0
7	e	MULTIPLES	0	0	2412	0	2412	1
8	f	WHOLESALER	0	1610	0	0	1610	1
9	f	WHOLESALER	0	0	0	0	0	0
10	f	WHOLESALER	0	0	0	0	0	0
11	g	MULTIPLES	0	0	2224	0	2224	1
12	g	WHOLESALER	0	1820	0	0	1820	0
13	h	DEPOT	2042	0	0	0	2042	1
14	i	DEPOT	608	0	0	0	608	0
15	i	DEPOT	1042	0	0	0	1042	1
16	j	MULTIPLES	0	0	2150	0	2150	1
17
18	9		3	2	3	1	FORMULA ANSWER
19
20	9		3	2	3	1	CORRECT ANSWER
Sheet1

Cell Formulas
Range	Formula
G2:G16	G2	=MAX(C2:F2)
H2:H16	H2	=(G2=MAX(IF(A$2:A$17=A2,G$2:G$16)))*(SUM(IF(A$2:A2=A2,IF(G$2:G2=G2,1)))=1)
A18	A18	=SUMPRODUCT(--(ISTEXT(UNIQUE(A2:A16))))
C18:F18	C18	=COUNTIFS(C2:C16,">0",$H$2:$H$16,"=1")

 

[kim2109]

Rob, genius. Thank you so much ♡

 

[RobP]

you're welcome, thanks for the feedback

Rob