Vlook up and Countif - working out the number of instances data is recorded

[OnyeI]

Hello All,

Have come completely unstuck on this one. Any help would be appreciated.

I am looking for a formula to look-up the name of an organisation – then count how many instances of an outcome they have data recorded against. So for instance, if I got the formula to work and did the look up (on the first table - attached) – the second table (also attached) would be the outcome. It sounds really straight forward, and I've tried to combine countif and v-lookup but just can’t make it work.

I have a huge spreadsheet of around 1500 rows and 500 columns.

Cheers,
Onye

 

[vletm]

OnyeI
for Cell B16
=COUNTIFS($A$3:$A$12,$A16,B$3:B$12,"<>")
and copy paste as needed
... 12 would be around 1500 with Your huge ...

 

[Chihiro]

... 12 would be around 1500 with Your huge ...

With COUNTIF(s)/SUMIF(s) family of function, it's safe to use entire column in formula (with some caveats).

=COUNTIFS($A:$A,$A16,B:B,"<>")

You can find good article on it below.
https://fastexcel.wordpress.com/201...erences-and-used-range-good-idea-or-bad-idea/

 

[OnyeI]

Thank you so, so much vletm and Chihiro (so logical once it's made clear!!).

Cheers,
Onye