CODEWITHSUNDEEP
excelexcel-formula
atg
atg

Reputation: 214

Count unique number of string based on condition in excel

I want to count unique strings based on condition in excel.

In the example below, column B to F are inputs and columns G and H is expected output.

Currently I am using formula for H6 =COUNTIFS(E1:E20, "<>", B1:B20, G6, C1:C20, "v1", D1:D20, "f") But with this formula 1 is counted 2 times, but I don't want to count duplicate entries.

Sample sheet image

How can I ignore duplicates and count only distinct values based on condition?

Upvotes: 0

Views: 169

Answers (2)

bosco_yip
bosco_yip

Reputation: 3802

In H6, array formula (Ctrl+Shift+Enter) copied down :

=SUM(N(FREQUENCY(IF(($B$1:$B$20=G6)*($C$1:$C$20="V1")*($D$1:$D$20="f"),$E$1:$E$20),$E$1:$E$20)>0))

enter image description here

Upvotes: 3

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

If you have Excel 2016+ with the UNIQUE function, you can use:

=SUMPRODUCT((INDEX(UNIQUE(input_arr),,1)=G6)*(INDEX(UNIQUE(input_arr),,2)="v1")*(INDEX(UNIQUE(input_arr),,3)="f")*(INDEX(UNIQUE(input_arr),,4)<>""))

where input_arr refers to =$B$1:$E$20

enter image description here

If you have an earlier version of Excel, I would suggest

  • Copy original data to a new location
  • Select the entire table, then "Remove Duplicates" ensuring all columns are selected
  • If new table is, for example, in B37:E37, use the formula:
=SUMPRODUCT((INDEX($B$27:$E$37,,1)=G6)*(INDEX($B$27:$E$37,,2)="v1")*(INDEX($B$27:$E$37,,3)="f")*(INDEX($B$27:$E$37,,4)<>""))

enter image description here

Upvotes: 2

Related Questions