Search code examples
google-sheetssumgoogle-sheets-formulaarray-formulascounting

How to Count Duplicate values based on other cell values in google sheet

How can I count Duplicates values based on other cell values look bellow

enter image description here

I have 3 duplicates in Column B but in Column C only 2 duplicates vales mention So I want to count remain duplicate Values in Column B which are not mentioned yet in Column C or in other words I want to count column B duplicates values which have an empty cell in Column C.
Look into Column D which get value 1 as we have only duplicate value in column B which have an empty cell in column C.
Hope you got my point.

Solution

  • try:

    =ARRAYFORMULA(SUM(N(COUNTIFS(B2:B, B2:B, ROW(B2:B), "<="&ROW(B2:B))>1))-
 COUNTIF(C2:C, "Duplicate"))

    0

    or:

    =ARRAYFORMULA(SUM(N(FILTER(COUNTIFS(B2:B, B2:B, ROW(B2:B), "<="&ROW(B2:B))>1, C2:C=""))))

    0