How can we help?

New post
1

Counting skus that appear in three data sets

Avatar
Joe Gullo
None
Follow

Hi Klip-friends

I am trying to count how many unique skus appear in three data sets.

Data set A is a list of 2000 best selling skus

Data set B is a list of skus on order into a warehouse

Data set C is a list of skus with inventory already in the warehouse

 

I am trying to get a count of how many skus in data set B are on data set A while simultaneously not on data set C

 

Is there any way to do this? Another user back in 2017 very helpfully provided this formula for counting values that appear in two data sets, I am missing the ability to exclude values from a third.

COUNTIF(  LOOKUP( GROUP( names_in_A ) , GROUP(names_in_B) , GROUP( names_in_B ) ) != BLANK() )

Many thanks

 

Date Votes

2 comments

  • Avatar
    Parker Selman Official comment

    Hi Joe, 

    Thanks for the post! 

    You can use COUNTIF, IN, AND, and NOT to set this up. Specifically with the formula below:

    COUNTIF(AND(IN(names_in_B , names_in_A) , NOT(IN(names_in_B , names_in_C))))

    This formula will only count values from B that appear in A but not in C. 

    I hope this helps! 

    Parker

  • 0
    Avatar
    Joe Gullo

    Thanks! I wasn't actually able to get your formula to give me a result but I changed it to and this seems to have worked

    COUNTIF(AND(IN(names_in_B, names_in_A) , NOT(IN(names_in_A, names_in_C)))) 

     

    I am not trying to do something display a list of the values that I was previously counting but have not been able to figure it out. Any ideas?

Please sign in to leave a comment.