SOLVED

SUM Employees using condition formula

Copper Contributor

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 NameCIDCountryContract EEOps Forecast & ActualsNum of Projects EE Size
Customer A010002United States209/1/202203 conditions = 1 Project300
Customer A010002United States2809/1/20221
Customer B010022Sweden405/1/20241 40
Customer C010038Egypt604/1/20241 60
Customer D010047Singapore337/1/20221 33
Customer E010072Spain13/1/20241 1
Customer F010109Austria758/1/20221 75
Customer G010109Brazil2006/1/202303 conditions = 1 Project358
Customer G010109Brazil1506/1/20230
Customer G010109Brazil86/1/20231

 

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

@romovaro 

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

sumproduct.png

@romovaro 

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

Riny_van_Eekelen_0-1714125676407.png

 

 

Thanks Oliver. It works :)
Thanks Riny.
1 best response

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

@romovaro 

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

sumproduct.png

View solution in original post