countifs not working in macro

[airforceone]

Sorry but I'm back again.... I hope everyone is well and good...

anyway I have 2 Sheet (Dummy and Updated) from Sheet Dummy my code would supposedly insert my Sum and CountIfs formula in the designated cell in my Sheet Updated, although it runs but the problem is the total value does not represent the actual figure from the Sheet Dummy, while the same formula inserted manually as the sample provided in Sheet Updated Column E to J works like a charmed! below is the sample table and code intended for your violent analysis and hopefully the right path to the correct code. Oh btw, I have 3 Variation of Formula in my VBA Code

DUMMY ZERO.xlsx
	A	B	C
1	SPECIE	COUNTRY	STATUS
2	American flamingo	Afghanistan	Hatched
3	Alcids	Afghanistan	Hatched
4	Amazon kingfisher	Afghanistan	Hatched
5	Albatrosses	Afghanistan	Sold
6	Albatrosses	Afghanistan	Sold
7	Accipiter hawks	Afghanistan	Sold
8	Accipiter hawks	Afghanistan	Sold
9	American dipper	Albania	Hatched
10	Accipiter hawks	Albania	Hatched
11	Albatrosses	Albania	Sold
12	American flamingo	Algeria	Hatched
13	Albatrosses	Algeria	Sold
14	Albatrosses	Algeria	Sold
15	Accipiter hawks	Algeria	Sold
16	American dipper	Andorra	Hatched
17	Alcids	Andorra	Hatched
18	Albatrosses	Andorra	Sold
19	Albatrosses	Andorra	Hatched
20	Accipiter hawks	Andorra	Sold
21	Albatrosses	Angola	Sold
22	Accipiter hawks	Angola	Sold
23	Albatrosses	Antigua and Barbuda	Sold
24	Albatrosses	Antigua and Barbuda	Sold
25	Albatrosses	Antigua and Barbuda	Sold
26	Albatrosses	Antigua and Barbuda	Sold
27	Albatrosses	Antigua and Barbuda	Sold
28	Albatrosses	Antigua and Barbuda	Sold
29	Accipiter hawks	Antigua and Barbuda	Sold
30	Accipiter hawks	Antigua and Barbuda	Sold
31	Accipiter hawks	Antigua and Barbuda	Sold
32	Accipiter hawks	Antigua and Barbuda	Sold
33	Accipiter hawks	Antigua and Barbuda	Sold
34	Albatrosses	Argentina	Sold
35	Albatrosses	Argentina	Sold
36	Accipiter hawks	Argentina	Sold
37	Accipiter hawks	Argentina	Sold
38	American dipper	Armenia	Hatched
39	American kestrel	Armenia	Hatched
40	American white pelican	Armenia	Hatched
41	American white pelican	Armenia	Hatched
42	American white pelican	Armenia	Hatched
43	Albatrosses	Armenia	Sold
44	Albatrosses	Armenia	Sold
45	Albatrosses	Armenia	Sold
46	Albatrosses	Armenia	Sold
47	Albatrosses	Armenia	Hatched
48	Albatrosses	Armenia	Sold
49	Albatrosses	Armenia	Sold
50	Alcids	Armenia	Hatched
51	Accipiter hawks	Armenia	Sold
52	Accipiter hawks	Armenia	Sold
53	Accipiter hawks	Armenia	Sold
54	American dipper	Australia	Hatched
55	American white pelican	Australia	Hatched
56	Accipiter hawks	Australia	Sold
57	Accipiter hawks	Australia	Hatched
58	American dipper	Austria	Hatched
59	American dipper	Austria	Hatched
60	American kestrel	Austria	Hatched
61	American white pelican	Austria	Hatched
62	American white pelican	Austria	Hatched
63	Albatrosses	Austria	Sold
64	Albatrosses	Austria	Sold
65	Albatrosses	Austria	Sold
66	Albatrosses	Austria	Sold
67	Albatrosses	Austria	Sold
68	Albatrosses	Austria	Sold
69	Albatrosses	Austria	Sold
70	Albatrosses	Austria	Sold
71	Accipiter hawks	Austria	Sold
72	Accipiter hawks	Austria	Sold
73	Accipiter hawks	Austria	Sold
74	Accipiter hawks	Austria	Sold
75	American kestrel	Azerbaijan	Hatched
76	Albatrosses	Azerbaijan	Sold
77	American dipper	Bahamas	Hatched
78	American white pelican	Bahrain	Hatched
79	American dipper	Bangladesh	Hatched
80	Alcids	Bangladesh	Hatched
81	Accipiter hawks	Bangladesh	Hatched
82	American dipper	Barbados	Hatched
83	American kestrel	Barbados	Sold
84	American kestrel	Barbados	Hatched
85	American white pelican	Barbados	Hatched
86	American white pelican	Barbados	Hatched
87	Alcids	Barbados	Hatched
88	Accipiter hawks	Barbados	Hatched
89	Albatrosses	Barbados	Sold
90	Albatrosses	Barbados	Sold
91	Albatrosses	Barbados	Sold
92	Accipiter hawks	Barbados	Sold
93	Accipiter hawks	Barbados	Sold
94	American dipper	Belarus	Hatched
95	American white pelican	Belarus	Hatched
96	American white pelican	Belarus	Hatched
97	American white pelican	Belarus	Hatched
98	American white pelican	Belarus	Hatched
99	American white pelican	Belarus	Hatched
100	Alcids	Belarus	Hatched
101	Accipiter hawks	Belarus	Hatched
102	Albatrosses	Belarus	Sold
103	Albatrosses	Belarus	Sold
104	Albatrosses	Belarus	Sold
105	Accipiter hawks	Belarus	Sold
106	Accipiter hawks	Belarus	Sold
107	Albatrosses	Belgium	Sold
108	Albatrosses	Belgium	Sold
109	Accipiter hawks	Belgium	Sold
110	Accipiter hawks	Belgium	Sold
111	Alcids	Belize	Sold
112	Albatrosses	Belize	Sold
113	Accipiter hawks	Belize	Sold
114	American white pelican	Benin	Hatched
115	American white pelican	Benin	Hatched
116	Alcids	Benin	Hatched
117	Amazon kingfisher	Benin	Sold
118	Albatrosses	Benin	Sold
119	Albatrosses	Benin	Sold
120	Albatrosses	Benin	Sold
121	Albatrosses	Benin	Sold
122	Accipiter hawks	Benin	Sold
123	Accipiter hawks	Benin	Sold
124	Accipiter hawks	Benin	Sold
125	Amazon kingfisher	Bhutan	Sold
126	Albatrosses	Bolivia	Sold
127	Albatrosses	Bolivia	Sold
128	Albatrosses	Bolivia	Sold
129	Accipiter hawks	Bolivia	Sold
130	Accipiter hawks	Bolivia	Sold
131	Accipiter hawks	Bolivia	Sold
132	Albatrosses	Bosnia and Herzegovina	Sold
133	Albatrosses	Bosnia and Herzegovina	Sold
134	American kestrel	Botswana	Hatched
135	Albatrosses	Botswana	Sold
136	Alcids	Botswana	Sold
Dummy

DUMMY ZERO.xlsx
	A	B	C	D	E	F	G	H	I	J
1	SPECIE	Afghanistan			Antigua and Barbuda			Barbados
2		Hatched	Sold	Total	Hatched	Sold	Total	Hatched	Sold	Total
3	American flamingo			0	0	0	0	0	0	0
4	Alcids			0	0	0	0	1	0	1
5	Amazon kingfisher			0	0	0	0	0	0	0
6	Albatrosses			0	0	6	6	0	3	3
7	Accipiter hawks			0	0	5	5	1	2	3
8	American dipper			0	0	0	0	1	0	1
9	American kestrel			0	0	0	0	1	1	2
10	American white pelican			0	0	0	0	2	0	2
11	Grand Total	0	0	0	0	11	11	6	6	12
12		VBA Encoded			Manually Encoded Formula / NON - VBA
UPDATED

Cell Formulas
Range	Formula
D3:D10,J3:J10,G3:G10	D3	=SUM(B3:C3)
E3:E10	E3	=SUM(COUNTIFS(Dummy!A2:A136,$A3,Dummy!B2:B136,$E$1,Dummy!C2:C136,"Hatched"))
F3:F10	F3	=SUM(COUNTIFS(Dummy!A2:A136,$A3,Dummy!B2:B136,$E$1,Dummy!C2:C136,"Sold"))
H3:H10	H3	=SUM(COUNTIFS(Dummy!A2:A136,$A3,Dummy!B2:B136,$H$1,Dummy!C2:C136,"Hatched"))
I3:I10	I3	=SUM(COUNTIFS(Dummy!A2:A136,$A3,Dummy!B2:B136,$H$1,Dummy!C2:C136,"Sold"))
B11:J11	B11	=SUM(B3:B10)

    Dim MyRangeA As Range, MyRangeB As Range, MyRangeC As Range
    Dim LastRow as long, eWan as integer
  
    LastRow = 136
    eWan = 3
    Lr0W = 10
  
'   SPECIE
    Set MyRangeA = Sheets("Dummy").Range("A2:A" & LastRow)
'   COUNTRY
    Set MyRangeB = Sheets("Dummy").Range("B2:B" & LastRow)
'   STATUS
    Set MyRangeC = Sheets("Dummy").Range("C2:C" & LastRow)

    iLOOP = 2
    Do While eWan <= Lr0W
  
    '    Formula A - 1
        Cells(eWan, iLOOP).Formula = "=SUM(COUNTIFS('Dummy'!$A$2:$A" & LastRow & ",$A3,'Dummy'!$B$2:$B" & LastRow & ",$B$1,'Dummy'!$C$2:$C" & LastRow & ",""Hatched""))"

    '    Formula A - 2
        Cells(eWan, iLOOP).Formula = "=SUM(COUNTIFS(Dummy!$A$2:$A" & LastRow & ",$A3,Dummy!$B$2:$B" & LastRow & ",$B$1,Dummy!$C$2:$C" & LastRow & ",""Hatched""))"     

    '    Formula B - 1
        Cells(eWan, iLOOP + 1).Formula = WorksheetFunction.Sum(Application.WorksheetFunction.CountIfs(MyRangeA, "$A3", MyRangeB, "$B$1", MyRangeC, "Hatched"))
          
        eWan = eWan + 1
    Loop

 

[Jeffrey Mahoney]

Is this red text correct?
Cells(eWan, iLOOP).Formula = "=SUM(COUNTIFS('Dummy'!$A$2:$A" & LastRow & ",$A3,'Dummy'!$B$2:$B" & LastRow & ",$B$1,'Dummy'!$C$2:$C" & LastRow & ",""Hatched""))"

 

[airforceone]

Is this red text correct?
Cells(eWan, iLOOP).Formula = "=SUM(COUNTIFS('Dummy'!$A$2:$A" & LastRow & ",$A3,'Dummy'!$B$2:$B" & LastRow & ",$B$1,'Dummy'!$C$2:$C" & LastRow & ",""Hatched""))"

that's correct mate, it reference to the country that specie belongs to...
You may or may not used my formula. What the code do is, Checks the specie under specific country with either "Hatched" or "Sold" status.

$A3 = Specie
$B$1 = Country

in my example I just Merge the Country cell address for easy reading...

 

[airforceone]

SOLVED!
the villain in the code is $A3 it doesn't change at all but all is good now! I just have to increment my row address!

Cells(eWan, iLOOP).Formula = "=SUM(COUNTIFS('Dummy'!$A$2:$A" & LastRow & ",$A3,'Dummy'!$B$2:$B" & LastRow & ",$B$1,'Dummy'!$C$2:$C" & LastRow & ",""Hatched""))"

 

[RoryA]

FYI, you don't need the SUM there as your COUNTIFS is only returning one result anyway. You also don't need a loop really.

 

[airforceone]

FYI, you don't need the SUM there as your COUNTIFS is only returning one result anyway. You also don't need a loop really.

hmm, very interesting mate and I'm interested
would you be kind enough to recode it? or at least push me to a more efficient coding?
it might make my code a bit faster (if you have time to spare)

 

[RoryA]

Like this:

Range(Cells(eWan, iLOOP), Cells(Lr0W, iLOOP)).Formula = "=COUNTIFS('Dummy'!$A$2:$A" & LastRow & ",$A3,'Dummy'!$B$2:$B" & LastRow & ",$B$1,'Dummy'!$C$2:$C" & LastRow & ",""Hatched"")"

 

[airforceone]

Like this:

Range(Cells(eWan, iLOOP), Cells(Lr0W, iLOOP)).Formula = "=COUNTIFS('Dummy'!$A$2:$A" & LastRow & ",$A3,'Dummy'!$B$2:$B" & LastRow & ",$B$1,'Dummy'!$C$2:$C" & LastRow & ",""Hatched"")"

thanks mate your code is much like Lightning McQueen! fast and accurate
but my columns and rows are dynamic, that may range from 35 to 165 columns and 15 up to 96 rows per month
inserting formula in that scenario would also entails using looping function.... I think for now I'll just stick with my looping method but thanks mate I'll definitely used it in small sample data, cheers.