COUNTIF ignoring duplicates across multiple columns

by

I’d like to find how to use the COUNTIFS function in excel to count instances of “C.B. Type 16” across multiple columns while ignoring duplicates. Determining whether or not they are duplicates are determined by two seperate columns. It’s a bit tricky. Here is a sample of the data:

Sample Data

ChatGPT has not been any help, so I have come to the humans.

  • 1

    Please post sample data using Markdown-Table-Generator also show what you have tried and what is your expected output!

    – 




If I am not mistaken, then the following formula should work, as it assumes per the tag posted in the OP, there is no Excel Constraints that said:

enter image description here

• Formula used in cell I7

=LET(
     _data, B7:G20,
     SUM(--(CHOOSECOLS(UNIQUE(WRAPROWS(TOCOL(_data),3)),2)=I6)))
  • Using LET() function to read the functions and variables applied more clearly and enhances the readability as well reduces the repetitions of functions or ranges.
  • _data variable refers to the range.
  • Using TOCOL() function to convert the array into a single range.
  • Using WRAPROWS() function to convert the array into # of rows * 3 columns.
  • Extracting unique items by using UNIQUE() function.
  • Using CHOOSECOLS() function so it can be compared with the cell reference I6 which reads C.B. Type 16 (which is in question for the counts).
  • Since it returns an array of TRUE & FALSE converting it to 1 and 0 using double unary --.
  • Lastly, wrapping within SUM() to get the total unique counts.

Leave a Comment