0

COUNT IF MULTIPLE CRITERIA IS A DYNAMIC RANGE

Raymond Richard

August 21, 2018 09:25

None

Follow

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

Phones         Radios                    0 Phones

Radios          Laptop                   3 Radios

                     Laptop

                     Radios

                     Radios

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?

 

• Facebook
• Twitter
• LinkedIn

2 comments

• 1
  
  Rahul Bura August 21, 2018 19:02

  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

• 0
  
  Raymond Richard August 23, 2018 09:52

  Thanks so much mate, worked perfectly.