Dynamic Cell Address on Countifs inside For Loops

[airforceone]

Good day mate!
Need help to make my row criteria dynamic

    For iCtr = 2 To LastRow
        ActiveSheet.Range("F" & iCtr).Formula = "=SUM(COUNTIFS('XVList'!$E$2:$E" & LRow & ",$A2,'XVList'!$H$2:$H" & LRow & ",""Female""))"    
    Next iCtr

how can I make dynamic entry for ",$A2,", what my idea is to make it like ,A & iCtr, or something to that effect, but running it does not count frequency

 

[airforceone]

Fixed it....

 

[Peter_SSs]

Fixed it....

Wondering how?

If you do the whole column at once instead of one row at a time it is both quicker/more efficient and Excel will automatically make the A2 dynamic.
Also, the SUM function is doing nothing in that formula so can be removed.
It would be something like this

Range("F2:F" & LastRow).Formula = "=COUNTIFS('XVList'!$E$2:$E" & LRow & ",$A2,'XVList'!$H$2:$H" & LRow & ",""Female"")"

 

[airforceone]

Wondering how?

If you do the whole column at once instead of one row at a time it is both quicker/more efficient and Excel will automatically make the A2 dynamic.
Also, the SUM function is doing nothing in that formula so can be removed.
It would be something like this

Range("F2:F" & LastRow).Formula = "=COUNTIFS('XVList'!$E$2:$E" & LRow & ",$A2,'XVList'!$H$2:$H" & LRow & ",""Female"")"

my code mate, which "fixed" my previous problem

Range("F" & iCtr).Formula = "=SUM(COUNTIFS('XVList'!$E$2:$E" & LRow & ",$A" & iCtr & ",'XVList'!$H$2:$H" & LRow & ",""Female""))"

remove sum function and tried your code, indeed that realy worked!
really appreciate it mate! but would you be kind enough to add counting empty or 0 value
i.e.
if Male then Add one to Male Column
if Female then Add one to Female Column
if Sex = Empty (or null or no value at all) then Add one to Male Column

and follow up question if you still have the time mate, why did A2 became dynamic? that baffled me!

 

[Peter_SSs]

would you be kind enough to add counting empty or 0 value
i.e.
if Male then Add one to Male Column
if Female then Add one to Female Column
if Sex = Empty (or null or no value at all) then Add one to Male Column

Have not seen any data or layout so have no idea what "Male Column" or "Female Column" is.
Also do not know if you mean add one for every male or just add one at the end.
What about some small dummy sample data & expected results with XL2BB and explain again in relation to that sample data?

why did A2 became dynamic?

It is just how vba & Excel work. Like asking if you put the formula =A1 in cell B1 and then copy B1 down the column, why does A1 become A2 then A3 etc in the formula? It just does.

 

[airforceone]

Have not seen any data or layout so have no idea what "Male Column" or "Female Column" is.
Also do not know if you mean add one for every male or just add one at the end.
What about some small dummy sample data & expected results with XL2BB and explain again in relation to that sample data?


It is just how vba & Excel work. Like asking if you put the formula =A1 in cell B1 and then copy B1 down the column, why does A1 become A2 then A3 etc in the formula? It just does.

ok mate, if sData.Sex is empty then count empty to Male Column of said Specie in tData.Male, using our formula above (countifs)

SPECIE	Sex
American flamingo	Male
Alcids	Female
Amazon kingfisher	Male
Albatrosses	Female
Albatrosses	Male
Accipiter hawks	Female
Accipiter hawks	Male
American dipper	Female
Accipiter hawks	Male
Albatrosses	Female
American flamingo	Male
Albatrosses	Female
Albatrosses	Male
Accipiter hawks	Female
American dipper	Male
Alcids	Female
Albatrosses	Male
Albatrosses
Accipiter hawks
Albatrosses	Female
Accipiter hawks	Male
Albatrosses	Female
Albatrosses	Male
Albatrosses	Female
Albatrosses	Male
Albatrosses	Female
Albatrosses	Male
Accipiter hawks	Female
Accipiter hawks	Male
Accipiter hawks	Female
Accipiter hawks	Male
Accipiter hawks	Female
Albatrosses	Male
Albatrosses	Female
Accipiter hawks	Male
Accipiter hawks	Female
American dipper	Male
American kestrel	Female
American white pelican	Male
American white pelican	Female
American white pelican	Male
Albatrosses	Female
Albatrosses	Male
Albatrosses	Female
Albatrosses	Male
Albatrosses	Female
Albatrosses	Male
Albatrosses	Female
Alcids	Male
Accipiter hawks	Female
Accipiter hawks	Male
Accipiter hawks	Female
American dipper	Male
American white pelican	Female
Accipiter hawks	Male
Accipiter hawks	Female
American dipper	Male
American dipper	Female
American kestrel	Male
American white pelican	Female
American white pelican	Male
Albatrosses	Female
Albatrosses	Male
Albatrosses	Female
Albatrosses	Male
Albatrosses	Female
Albatrosses	Male
Albatrosses	Female
Albatrosses	Male
Accipiter hawks	Female
Accipiter hawks	Male
Accipiter hawks	Female
Accipiter hawks	Male
American kestrel	Female
Albatrosses	Male
American dipper	Female
American white pelican	Male
American dipper	Female
Alcids	Male
Accipiter hawks	Female
American dipper	Male
American kestrel	Female
American kestrel	Male
American white pelican	Female
American white pelican	Male
Alcids	Female
Accipiter hawks	Male
Albatrosses	Female
Albatrosses	Male
Albatrosses	Female
Accipiter hawks	Male
Accipiter hawks	Female
American dipper	Male
American white pelican	Female
American white pelican	Male
American white pelican	Female
American white pelican	Male
American white pelican	Female
Alcids	Male
Accipiter hawks	Female
Albatrosses	Male
Albatrosses	Female
Albatrosses	Male
Accipiter hawks	Female
Accipiter hawks	Male
Albatrosses	Female
Albatrosses	Male
Accipiter hawks	Female
Accipiter hawks	Male
Alcids	Female
Albatrosses	Male
Accipiter hawks	Female
American white pelican	Male
American white pelican	Female
Alcids	Male
Amazon kingfisher	Female
Albatrosses	Male
Albatrosses	Female
Albatrosses	Male
Albatrosses	Female
Accipiter hawks	Male
Accipiter hawks	Female
Accipiter hawks	Male
Amazon kingfisher	Female
Albatrosses	Male
Albatrosses	Female
Albatrosses	Male
Accipiter hawks	Female
Accipiter hawks	Male
Accipiter hawks	Female
Albatrosses	Male
Albatrosses	Female
American kestrel	Male
Albatrosses	Female
Alcids	Male

Specie	Male	Female
Alcids
Amazon kingfisher
Albatrosses
Accipiter hawks
American dipper
American kestrel
American white pelica

 

[airforceone]

It is just how vba & Excel work. Like asking if you put the formula =A1 in cell B1 and then copy B1 down the column, why does A1 become A2 then A3 etc in the formula? It just does.

stupid me! just like dragging cell value across range! got it mate

 

[Peter_SSs]

Looks like you missed this bit

some small dummy sample data & expected results

Also, can you use Mini Sheet instead of Table Only so we can see what columns/rows are actually involved? You appear to have given adjacent column but originally it was non-adjacent columns E & H.
Also, what is sData and tData - neither mentioned or shown before?

Please remember that while your worksheets and data are familiar to you, we know nothing about them apart from what you specifically tell or (better) show us.