Mode Ifs - just can't get it going

[MJFL]

Afternoon all,

Long time lurker first time poster - a lot of you have already really helped me out but don't know it! Thank you one and all.

I'm at my wits end with a conditional mode query.

in column A I have the name of a product (text) There are around 1k products that show up multiple times in around 25k total rows
in column B I have the customer name there are about 200 customer names (likely not relevant).
in column C I have the number of times that product has been bought by that said customer

I'm trying to get to a point where I can how often (the mode of number of times) a product is purchased

I've tried the =mode(if(A1:A25000=A2,$C$1:$C$25000)) (as an array) but I've hit a brick wall as I think this is only looking at the one line

I had this dream there would be the equivalent of an averages I could just bung in wiht my criteria but that's not the case.

Any advice or words of wisdom gratefully received!

 

[JamesCanale]

MrExcelPlayground11.xlsx
	A	B	C	D
1	Product	Doesn'tMatter	Units bought	Mode of units bought by product
2	A		4	8
3	B		5	5
4	C		6	6
5	D		7	7
6	A		8	8
7	C		9	9
8	G		3	3
9	H		4	4
10	A		8	8
11	Z		6	6
Sheet3

Cell Formulas
Range	Formula
D2:D11	D2	=IFNA(MODE(FILTER($C$2:$C$11,$A$2:$A$11=A2)),C2)

Or you might have a UNIQUE list of column A elsewhere, with the mode calc there.

 

[MJFL]

Heya James - thanks for the reply sorry for my slow response - I'll give that whirl and get back to you with an update - it reads like it makes sense and should work so my fingers are firmly crossed!

 

[MJFL]

James you legend it works like a charm thank you!