I want to be able to count the number of times an item in column A have appeared in column B and bring the result in column C. But values from column A have been obtained using a formula so they are expected to change every time.

Eg:

Column A      Column B              Column C

Laptops         Umbrella               2 Laptops

Laptop

Column C will show only numbers don't bother about the words. Now is there a way I can select an item in the row as a reference for count if? Say COUNTIF(@Column B:B=ARRAY(@ColumnA:A)).

Long story short, how do I make it work?

• Rahul Bura

Hey Raymond,

Thanks for reaching out! :) You should be able to make it work with a LookUp in Column C.

The LookUp function has the following definition: LOOKUP(input, keys, results). In your case here are what should be used:

- Input: This would be the results reference of Column A(&ColumnA:A). The assumption is that in Column A, each entry is unique. If it isn't you may need to use a Group in Column A to remove duplicates.

- Keys: Here would be your reference to column B- you need Group(B:B)

- Results: Here you would use the CountDisnct of column B so you'd have Countdistinct(B:B)

So what this will do is count the number of each item in column B and output it in the order of the items in column A:A

• Raymond Richard

Thanks so much mate, worked perfectly.