How can we help?

New post
0

COUNT IF MULTIPLE CRITERIA IS A DYNAMIC RANGE

Avatar
Raymond Richard
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?

 

Date Votes

2 comments

  • 1
    Avatar
    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

  • 0
    Avatar
    Raymond Richard

    Thanks so much mate, worked perfectly.

Please sign in to leave a comment.