Forum Discussion
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 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.
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.
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.
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.
- This seems to work! thank you!
Sheilveil , you are welcome