SOLVED

# SUM Employees using condition formula

Copper Contributor

# SUM Employees using condition formula

HI

I have an issue with one formula. I created a formula to know the number of projects i have within a FY

Definition of project:

IF SAME CUSTOMER, IF SAME COUNTRY and Same GO Live Date = 1, otherwise 0

I custom sorted my file with Customer, Country and Go lIve Date and used the formula below comparing previous row.

=IF(B2<>B3,1,IF(AND(B2=B3,BE2<>BE3),1,IF(AND(B2=B3,D2=D3,BE2=BE3),0,1)))

I have a column called Contract EE (Employees) and I have been asked to filter projects with max 50employees, 100 employees and + 100 employees but I am struggling to add this SUM in my formula. Any help? What I need is the image below showing number of employees for every count of "1" using the formula with the 3 conditions.

 Client Name CID Country Contract EE Ops Forecast & Actuals Num of Projects EE Size Customer A 010002 United States 20 9/1/2022 0 3 conditions = 1 Project 300 Customer A 010002 United States 280 9/1/2022 1 Customer B 010022 Sweden 40 5/1/2024 1 40 Customer C 010038 Egypt 60 4/1/2024 1 60 Customer D 010047 Singapore 33 7/1/2022 1 33 Customer E 010072 Spain 1 3/1/2024 1 1 Customer F 010109 Austria 75 8/1/2022 1 75 Customer G 010109 Brazil 200 6/1/2023 0 3 conditions = 1 Project 358 Customer G 010109 Brazil 150 6/1/2023 0 Customer G 010109 Brazil 8 6/1/2023 1

4 Replies
best response confirmed by romovaro (Copper Contributor)
Solution

# Re: SUM Employees using condition formula

=SUMPRODUCT((\$A\$2:\$A\$11=A2)*(\$C\$2:\$C\$11=C2)*(\$E\$2:\$E\$11=E2)*\$D\$2:\$D\$11)

=SUMIFS(\$D\$2:\$D\$11,\$A\$2:\$A\$11,A2,\$C\$2:\$C\$11,C2,\$E\$2:\$E\$11,E2)

SUMPRODUCT and SUMIFS return the expected result in my sheet. The formula is in cell I2 and filled down.

# Re: SUM Employees using condition formula

Alternatively, use a pivot table as shown in the attached file.

# Re: SUM Employees using condition formula

Thanks Oliver. It works 🙂

# Re: SUM Employees using condition formula

Thanks Riny.
1 best response

Accepted Solutions
best response confirmed by romovaro (Copper Contributor)
Solution

# Re: SUM Employees using condition formula

=SUMPRODUCT((\$A\$2:\$A\$11=A2)*(\$C\$2:\$C\$11=C2)*(\$E\$2:\$E\$11=E2)*\$D\$2:\$D\$11)

=SUMIFS(\$D\$2:\$D\$11,\$A\$2:\$A\$11,A2,\$C\$2:\$C\$11,C2,\$E\$2:\$E\$11,E2)

SUMPRODUCT and SUMIFS return the expected result in my sheet. The formula is in cell I2 and filled down.