COUNTIFS and SUMPRODUCT yield different answers from same data source

[WildWill]

Hi

I have a dataset that contains a wide range of HR-related information (could not post it here using Excel Jeanie due to size). From this same data set, two different formulae are giving two different answers, and this is driving me nuts. I have looked at all the possible causes in terms of data integrity etc. and I just cant understand why these would yield different answers. Please help.

The items being measured in both cases are totals of Rig-Based Employees (a) and Totals of Non-Rig Employees (b). If I use the SUMPRODUCT formulae below for respectively (a) and (b) , I get (a) = 165 and (b) = 25:

formula for a: =SUMPRODUCT((Source!$D$4:$D$203=SUMPRODUCT!A2)*(Source!$G$4:$G$203<>"Leaver")*(Source!$G$4:$G$203<>"Transfer Out"))
formula for b: =SUMPRODUCT((Source!$D$4:$D$203=SUMPRODUCT!A3)*(Source!$G$4:$G$203<>"Leaver")*(Source!$G$4:$G$203<>"Transfer Out"))

Now, with the same dataset, a different approach using a COUNTIFS formula (below) yields (a) = 166 and (b) = 24:

(Again, this is complicated to show without being able to attach the worksheet, but the below formula is repeated in a table for all the different permutations of data)

=SUM(COUNTIFS(Source!$C$4:$C$203,COUNTIFS!C4,Source!$B$4:$B$203,"Non-Local",Source!$F$4:$F$203,{"Expat","TCN"},Source!$G$4:$G$203,{"Starter";"Transfer In";""}))
Is there any other way that I can share the sheets here? sure that will simplify what I am trying to show.

Will

 

[GerryZ]

Hello!
Your formula works fine

 

[WildWill]

Thanks Jonmo1 and GerryZ for all your help! I am going to add some more error checking features to the base data sheet so that those types of mistakes are identified upfront.

 

[Jonmo1]

You're welcome