Calculating SumIfs from another tab
Simply, in this case, there's no real reason to use the worksheet function if you're not putting the function into the cell.

Flawed Worksheet_Change structure
This here -

ActiveCol = Left(ActiveCell.EntireColumn.Address(False, False), InStr(1, ActiveCell.EntireColumn.Address(False, False), &quot;:&quot;) - 1)


Only works on the worksheet change, so if you don't go directly to the next cell (vertically), you get the wrong column. Try it -
ActiveCol = Left(ActiveCell.EntireColumn.Address(False, False), InStr(1, ActiveCell.EntireColumn.Address(False, False), &quot;:&quot;) - 1)
MsgBox ActiveCol

It's the column wherever the current cell is selected, since the change doesn't occur until you leave the cell, not where the target cell was. Not sure why you need the letter, so just get the column number -
ActiveCol = Target.Column
MsgBox ActiveCol

This works on your target that changed, not where your cursor ended up. Small, but important.
This also matters for your TabToTake using ActiveCell, are you sure you want to take the next cell's value, which best case scenario, will be on row 4?
That's why you pass Target into the function - to use it as a variable. I know that seems pretty obvious when I point it out, but I also know how confusing Target can be if you're just starting out, so don't sweat it.
If Not Intersect(Target, Rows(3)) Is Nothing Then
    targetSheet = Sheets(Target.Value)
    activeColumn = Target.Column


You haven't qualified everything -

For Each CellsCnt In Range(&quot;E4:E2500&quot;).Cells


I imagine that should be on the current sheet, but tell me that.

Let's break down the meat of your sub
Set ID = Sheets(TabToTake).Range(&quot;A:A&quot;)
Set Amount = Sheets(TabToTake).Range(&quot;Q:Q&quot;)
For Each CellsCnt In Range(&quot;E4:E2500&quot;).Cells
    If Not IsEmpty(CellsCnt) Then
        Cells(CellsCnt.Row, ActiveCol).Value = Application.WorksheetFunction.SumIfs(Amount, ID, Cells(CellsCnt.Row, &quot;E&quot;).Value)
    End If
Next CellsCnt

First, For every cell in column E on targetSheet, if it isn't empty THEN
targetSheet, of that cell, in the ActiveCol (which is not target) =
Application.WorksheetFunction.SumIfs(targetSheet.Range(&quot;Q:Q&quot;), targetSheet.Range(&quot;A:A&quot;), ActiveSheet.Cells(CellsCnt.Row, 5)).Value

So what are we doing here, exactly.

Summing all of targetSheet.Columns(&quot;G&quot;) if all of targetSheet.Columns(&quot;A&quot;) = current cell. FOR ALL OF COLUMN E.

Assuming that a lot of column E changes when something in row 3 changes, you essentially do a sum of vlookups yeah? Hm, why do that so much? Not knowing how your cells are linked up in your sheet, makes it more difficult to really optimize this, but here's a try
Const LOOKUP_COLUMN As Long = 5
Const LOOK_IN_COLUMN As Long = 1
Const SUM_COLUMN As Long = 17
Dim lookupVector As Variant
Dim lookinVector As Variant
Dim sumVector As Variant
Dim lastRow As Long

lastRow = ActiveSheet.Cells(Rows.Count, LOOKUP_COLUMN).End(xlUp).Row
lookupVector = ActiveSheet.Range(Cells(4, LOOKUP_COLUMN), Cells(lastRow, LOOKUP_COLUMN))

lastRow = targetSheet.Cells(Rows.Count, LOOK_IN_COLUMN).End(xlUp).Row
lookinVector = targetSheet.Range(Cells(1, LOOK_IN_COLUMN), Cells(lastRow, LOOK_IN_COLUMN))
sumVector = targetSheet.Range(Cells(1, SUM_COLUMN), Cells(lastRow, SUM_COLUMN))

Now everything is in arrays and you can perform your calculations behind the scenes, much faster -
Dim resultVector() As Double
Dim result As Double
ReDim resultVector(LBound(lookupVector) To UBound(lookupVector))
Dim index As Long
Dim targetIndex As Long
For index = LBound(lookupVector) To UBound(lookupVector)
    result = 0
    For targetIndex = LBound(lookinVector) To UBound(lookinVector)
        If lookinVector(index) = lookupVector(index) Then result = result + sumVector(targetIndex)
    Next
    resultVector(index) = result
Next

Once you're done, you just put that resultVector back in your lookup_column.

Variables
Give your variables meaningful names. When I see Amount in code, I don't assume it's a Range I assume it's a Value. Make that clear, it's easier on future you. You can see my examples of changes in the code above.
Standard VBA naming conventions have camelCase for local variables and PascalCase for other variables and names.

Dim ActiveCol


You didn't give this variable a type so it was declared as a variant. You don't need a variant, just a simple long.

Indenting
It's good practice to indent all of your code that way Labels will stick out as obvious.

Error Handling
This

On Error Resume Next


What error is that handling? What can go wrong? For instance, when you were using the selection instead of the target, you wouldn't see that it didn't do what it was supposed to do. Handle your errors specifically so you know what can go wrong, how it goes wrong and how to prevent it.

Working on the sheet
Working with the ranges instead of arrays - it just slows the code down by needing to fiddle with the spreadsheet while doing everything else behind the scenes. There's a good question on StackOverflow addressing this. The same logic applies here.

Comments
Comments - &quot;code tell you how, comments tell you why&quot;. The code should speak for itself, if it needs a comment, it might need to be made more clear. If not, the comment should describe why you're doing something rather than how you're doing it. Here are a few reasons to avoid comments all together.

Dim Amount As Range 'the range i want to sum
Dim ID As Range 'criteria range
Dim Arg3 As Variant 'the criteria


You telling me in comments what ranges do what, that was your first indication that your naming could be improved, right?

Method
Like I said, I don't know the exact structure of your sheets, but maybe Worksheet_Change isn't the best way in which to do these calculations. Think about what changes and when it changes and see if there's a more efficient way to do it instead of every time there's a change.
Honestly, maybe your best bet is actually using the SUMIFS function, like
 =IFERROR(&quot;&quot;,SUMIFS(Sheet1!A1:A31,Sheet1!Q1:Q25,Sheet2!E7))