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