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!
if you could not post it here using Excel Jeanie why don't you use Mr.excel.HTMLMaker
Please provide details of data layout with just few rows and colunms with the expected outcome so we can understand better?
I suggest you to post a nice HTML screen-shot with Mr.Excel HTML Maker (not a picture), if you don’t know how to istall and how to use it watch the video linked in my signature!!
help us to help you
Thank you in advance!

 

[Jonmo1]

Now, with the same dataset

But... They're NOT the same dataset...

The sumproduct formulas are referring to
Source!$D$4:$D$203 and Source!$G$4:$G$203

The countifs is referring to
Source!$C$4:$C$203 and Source!$B$4:$B$203 and Source!$G$4:$G$203


They're not even doing the same thing,
sumproduct is counting rows where D = A2 AND G does not = Leaver or Transfer Out
countifs is counting rows where C = C4, AND B = Non Local AND F = Expat or TCN AND G = Starter Transfer in or ""


I don't understand your surprise that the 2 formulas give different results.

 

[WildWill]

Thanks GerryZ - I will upload as recommended.

 

[WildWill]

Hi Jonmo1

Yes, I do realize that - but the two different sets of formulae approach the data in completely different manners, and it is the fact that I cant easily show the tabe that is being used in the COUNTIFS approach, that is (hopefully) the missing part. I will upload using Mr Excel Maker.

 

[Jonmo1]

This formula
=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 NOT an equivelant of this formula
=SUMPRODUCT((Source!$D$4:$D$203=SUMPRODUCT!A2)*(Source!$G$4:$G$203<>"Leaver")*(Source!$G$4:$G$203<>"Transfer Out"))


This would be
=SUM(COUNTIFS(Source!$D$4:$D$203,SUMPRODUCT!A2,Source!$G$4:$G$203,<>"Leaver",Source!$G$4:$G$203,"<>Transfer Out"))

 

[WildWill]

Thanks GerryZ - I will upload as recommended.

GerryZ - I am struggling to get the Mr Excel Maker to work, can you use a Dropbox link where I have placed the sheet? https://www.dropbox.com/sh/rqmlwqgnmnd14gc/AADbXJrq9wrsH5dLdWg3g24La?dl=0

 

[WildWill]

Jonmo1

The SUMPRODUCT formulae search directly on the source data table, while the COUNTIFS formula is applied to a table layout where the source data is first calculated segments according to where the employee works, and what type of employee we are dealing with, this is why the formulae don't appear the same. But I am aware of what you are saying, it's just difficult for me to express without showing you the actual spread sheet. As indicated before, I have placed in in Dropbox with this link, can you use that please? https://www.dropbox.com/sh/rqmlwqgnmnd14gc/AADbXJrq9wrsH5dLdWg3g24La?dl=0

 

[Jonmo1]

D190 on Source Sheet is MisLabled as Non-Rig.

 

[GerryZ]

Hello!
Your formula works fine
I tried with define name so I don't have to jump sheet by sheet

Book1
	A	B
1
2	Rig	165
3	Non-Rig	25
4
5
6
7
8
9
10	B=payrooll
11	C= personal sub area
12	D= allocation
13	F=Employment Class
14	G=Movement Type
SUMPRODUCT

Cell Formulas
Range	Formula
B2	=SUMPRODUCT((Allocation=A2)*(Movement_Type<>"Leaver")*(Movement_Type<>"Transfer Out"))

Named Ranges
Name	Refers To	Cells
Allocation	=Source!$D$4:$D$203
Movement_Type	=Source!$G$4:$G$203

Book1
	B	C	D	E	F	G
3	Payroll	Personnel Sub Area	Allocation	Contract Type	Employment Class	Movement Type
4	Local	Finance & Admin	Non-Rig		Local
5	Local	Global Sup Mgt	Non-Rig		Local
6	Local	Global Sup Mgt	Non-Rig		Local
7	Local	Global Sup Mgt	Non-Rig		Local
8	Local	Maintenance	Non-Rig		Local
9	Local	HR	Non-Rig		Local	Leaver
10	Local	HR	Non-Rig		Local	Leaver
11	Local	HR	Non-Rig		Local	Leaver
12	Local	HR	Non-Rig		Local
13	Local	HR	Non-Rig		Local
14	Local	T-221	Rig		Local
15	Local	T-221	Rig		Local
16	Local	T-221	Rig		Local
17	Local	T-221	Rig		Local
18	Local	T-221	Rig		TCN
19	Local	T-221	Rig		Local
20	Local	T-221	Rig		Local
21	Local	T-221	Rig		Local
22	Local	T-221	Rig		Local
23	Local	T-221	Rig		Local
24	Local	T-221	Rig		Local
25	Local	T-221	Rig		Local
26	Local	T-221	Rig		TCN
27	Local	T-221	Rig		Local
28	Local	T-221	Rig		Local
29	Local	T-221	Rig		Local
30	Local	T-221	Rig		Local
31	Local	T-221	Rig		Local	Leaver
32	Local	T-221	Rig		Local
33	Local	T-221	Rig		Local
34	Local	T-221	Rig		Local
35	Local	T-221	Rig		Local
36	Local	T-221	Rig		Local
37	Local	T-221	Rig		Local
38	Local	T-221	Rig		Local
39	Local	T-221	Rig		Local
40	Local	T-221	Rig		Local
41	Local	T-221	Rig		Local
42	Local	T-221	Rig		Local
43	Local	T-221	Rig		Local
44	Local	T-221	Rig		Local
45	Local	T-221	Rig		Local
46	Local	T-221	Rig		Local
47	Local	T-221	Rig		Local
48	Local	T-221	Rig		Local
49	Local	T-221	Rig		Local
50	Local	T-221	Rig		Local
51	Local	T-221	Rig		Local
52	Local	T-221	Rig		Local
53	Local	T-221	Rig		Local
54	Local	T-221	Rig		Local
55	Local	T-221	Rig		Local
56	Local	T-221	Rig		Local
57	Local	T-221	Rig		Local
58	Local	T-221	Rig		Local
59	Local	T-221	Rig		Local
60	Local	T-221	Rig		Local
61	Local	T-221	Rig		Local
62	Local	T-221	Rig		Local
63	Local	T-221	Rig		Local
64	Local	T-221	Rig		Local
65	Local	T-221	Rig		Local
66	Local	T-221	Rig		Local
67	Local	T-221	Rig		Local
68	Local	T-221	Rig		Local
69	Local	T-221	Rig		Local
70	Local	T-221	Rig		Local
71	Local	T-221	Rig		Local
72	Local	T-221	Rig		Local
73	Local	T-221	Rig		Local
74	Local	T-221	Rig		Local
75	Local	T-221	Rig		Local
76	Local	T-221	Rig		Local
77	Local	T-221	Rig		Local
78	Local	T-63	Rig		Local
79	Local	T-63	Rig		Local
80	Local	T-63	Rig		Local
81	Local	T-63	Rig		Local
82	Local	T-63	Rig		Local
83	Local	T-63	Rig		Local
84	Local	T-63	Rig		Local
85	Local	T-63	Rig		Local
86	Local	T-63	Rig		Local
87	Local	T-63	Rig		Local
88	Local	T-63	Rig		Local	Leaver
89	Local	T-63	Rig		Local
90	Local	T-63	Rig		Local
91	Local	T-63	Rig		Local
92	Local	T-63	Rig		Local
93	Local	T-63	Rig		Local
94	Local	T-63	Rig		Local
95	Local	T-63	Rig		Local
96	Local	T-63	Rig		Local
Source