Counting unique text values

[candymycandy]

Hi,

Briefly, I have an excel tab that has events (Conferences, training etc), people attending and what company they are from.
I need:

• a formula that will pull unique text values (for the company) from the list and display them in another sheet's cell (not the "unique" function as this doesn't allow me to sort by a-z).
• a formula that will count the number of unique events that each company has booked onto to display in the same sheet as above

This forms part of a wider dashboard and for various reasons a pivot isn't working for what I need. I have uploaded an example of what I need.

Hope this is clear and many thanks in advance!

 

[Georgiboy]

Maybe:

example data.xlsx
	A	B	C	D	E
1	I need formula to pull unique values from Data Tab Col C to go in colA below				Formula to calculate how many events each company has booked
2	Company	Training Budget	Used	Remaining	Events Booked
3	Asda	5000	1000	4000	3
4	Big Co Shops	1000	500	500	1
5	Chems	4000	400	3600	3
6	Etsy	3000	2000	1000	1
7	Home Stores	1000	900	100	2
8	Londis	10000	1210	8790	1
9	OnlineTrading	4400	2121	2279	2
10	Smiths	5000	1000	4000	1
11	Tesco	1500	1400	100	1
Formula here

Cell Formulas
Range	Formula
A3:A11	A3	=LET(s,SORT(UNIQUE(Data!C:C)),FILTER(s,(s<>"")*(s<>"Company")))
E3:E11	E3	=COUNTIF(Data!C:C,'Formula here'!A3#)
D3:D11	D3	=B3-C3
Dynamic array formulas.

 

[candymycandy]

Hi and thank you!

The LET formula is working perfectly, thanks

I don't think I explained myself properly for:

• a formula that will count the number of unique events that each company has booked onto to display in the same sheet as above

I am not sure if its possible, but I need to count how many events each Company has signed up for, rather than how many attendees they sent.
E.G. Amazon sent 2 staff to "X event", and 3 staff to "Z event". So the result I need to see is that Amazon have signed up to 2 events.

There is a strong chance that I'm not using the formula correctly!
Many thanks!