Forum Discussion

Sheilveil's avatar
Sheilveil
Copper Contributor
Jul 24, 2023
Solved

Count If formula with no duplicates and one condition

I am using MS Office professional Plus 2016 and I am trying to create a count with the below criteria: 1) Employee name (column E) is not duplicated and 2) The value in column G is greater than 0 ...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 24, 2023

    Sheilveil 

    I guess something like this

    I have no Excel 2016 to test, perhaps that could be

    =SUMPRODUCT(
    IF(
        COUNTIFS(
            $E$6:$E$1991, $E$6:$E$1991,
            $G$6:$G$1991, "<>"
        ),
        1 / COUNTIFS($E$6:$E$1991, $E$6:$E$1991 & ""),
        0
    )
)

    or bit shorter

    =SUM(
    IF(
        COUNTIFS(
            $E$6:$E$1991, $E$6:$E$1991,
            $G$6:$G$1991, "<>"
        ),
        1 / COUNTIFS($E$6:$E$1991, $E$6:$E$1991 & ""),
        0
    )
)

    since SUMPRODUCT with IF shall be used as array formula, i.e. you shall enter it with Ctrl+Shift+Enter instead of Enter. If array formula in any case, SUM works the same way.

     

    Please check in attached file.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 24, 2023

Sheilveil 

First formula counts number of duplicates. If count values which appear exactly once (no duplicates) it could be

=SUMPRODUCT( ($E$6:$E$1991<>"")*(COUNTIF($E$6:$E$1991,$E$6:$E$1991&"") = 1 ) )

Count values ignoring blanks

=COUNTIF($G$6:$G$1991,"<>")

 

  • Sheilveil's avatar
    Sheilveil
    Copper Contributor
    Jul 24, 2023
    Thank you.
    I actually do need it to count the duplicates. I'm trying to find the number of employees, therefore each pay period will have duplicates, and I want it to count only those employees in E that have hours in H.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 24, 2023

      Sheilveil 

      I guess something like this

      I have no Excel 2016 to test, perhaps that could be

      =SUMPRODUCT(
    IF(
        COUNTIFS(
            $E$6:$E$1991, $E$6:$E$1991,
            $G$6:$G$1991, "<>"
        ),
        1 / COUNTIFS($E$6:$E$1991, $E$6:$E$1991 & ""),
        0
    )
)

      or bit shorter

      =SUM(
    IF(
        COUNTIFS(
            $E$6:$E$1991, $E$6:$E$1991,
            $G$6:$G$1991, "<>"
        ),
        1 / COUNTIFS($E$6:$E$1991, $E$6:$E$1991 & ""),
        0
    )
)

      since SUMPRODUCT with IF shall be used as array formula, i.e. you shall enter it with Ctrl+Shift+Enter instead of Enter. If array formula in any case, SUM works the same way.

       

      Please check in attached file.

      • Sheilveil's avatar
        Sheilveil
        Copper Contributor
        Jul 25, 2023
        This seems to work! thank you!

Resources