SUM Employees using condition formula

[romovaro]

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

[#2 · Accepted 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.

[#3]

@romovaro 

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

[#4]

Thanks Oliver. It works 🙂

[#5]

Thanks Riny.