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

 

I think I was able to find a formula to get the non-duplicated count for column E using =SUMPRODUCT(($E$6:$E$1991<>"")/COUNTIF($E$6:$E$1991,$E$6:$E$1991&""))   but the number didn't change if I removed some non-duplicate lines.

 

And a formula to count column G using =COUNTIF($G$6:$G$1991,">0")

but I need it to count column E if there is a number in column G.

 

See small sample below (there are 12 payrolls) - I was not able to figure out how to upload a sample document.

 

Column EEMT/PMColumn G
   
MarkEMT8.08
ScottPM8.05
TammyEMT24.00
AnthonyEMT32.43
DeanPM 
RobertPM 
RylieEMT 
LaShawnaEMT37.87
JosephEMT15.63
JohnEMT31.55
HollyPM 
AlexandraEMT8.25
NicolePM 
CorinnePM 
JessePM 
StuartPM 
DeanPM32.27
ChristopherPM25.18
NicholasEMT8.15
MarkEMT25.00
   
   
  256.46
   
   
   
MarkEMT24.52
ScottPM16.58
TammyEMT46.60
AnthonyEMT31.35
JesseEMT 
DeanPM 
RobertPM 
RylieEMT37.13
LaShawnaEMT42.80
JosephEMT15.83
JohnPM 
HollyEMT16.10
AlexandraPM 
NicolePM 
CorinneEMT 
MadelineEMT 
ChristopherEMT 
MichaelEMT 
BrittanyPM 
JessePM 
StuartPM22.90
DeanEMT 
MelissaPM42.78
ChristopherEMT 
NicholasEMT 
AlyssaEMT8.22
MarkEMT 
EricEMT 
Kenneth  
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.

5 Replies

  • 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.

Resources