VBA Sumifs with Multiple Criteria and Dynamic Ranges

[lamarh755]

I need help troubleshooting code that I thought would work, but I am getting an error message under the formula stating "identifier under cursor is not recognized."

I have a spreadsheet that has 2 sheets (CMPLX-DOWN and CMPLX-DOWN Pivot)

I am trying to have a sumifs formula in "CMPLX-DOWN Pivot" Range("K10:K and lastrow) that looks at the following...

1) Sum the total number of cases in Sheets("CMPLX-DOWN).Range("AM8:AM" and lastrow) where the quantity in column "DR8:DR" and lastrow is greater than or equal to 3.00, where the value in ("CMPLX-DOWN Pivot").Range("G10:G" and lastrow) matches the value in Sheets(CMPLX-DOWN).Range("A8:A" and lastrow).

I have the following code listed below.


Sub Macro90()
Dim eLastRow As Long

Set ws = ThisWorkbook.Sheets("CMPLX-DOWN")
Set ws1 = ThisWorkbook.Sheets("CMPLX-DOWN Pivot")

eLastRow = ws.Cells(Cells.Rows.Count, "A").End(xlUp).row
gLastRow = wsa.Cells(Cells.Rows.Count, "G").End(xlUp).row



Set rng1 = ws.Range("AM8:AM" & eLastRow)
Set rng2 = ws.Range("DR8:DR" & eLastRow)
Set rng3 = ws.Range("A8:A" & eLastRow)
Set rng4 = wsa.Range("G10:G" & gLastRow)



ws1.Range("K10") = WorksheetFunction.SumIfs(rng1, rng2, ">=3.00", rng3, rng4)

Any assistance would be greatly appreciated.

Thank you,
Lamar

 

[DanteAmor]

This type of search cannot be solved simply with the sumifs function, since you must first look for the value in column A in column G of the other sheet, and it may exist in a different row.
You could solve this problem by using an auxiliary column to obtain the result of whether it exists or not, and then you could use the sumifs formula.
For example, if you had these values in the "pivot" sheet:

varios 05abr2025.xlsm
	A					F	G	H
1
9
10							Q
11							W
12							E
13							R
14							A
15
CMPLX-DOWN Pivot

The formula in K10 could be like this:

varios 05abr2025.xlsm
	A	B								J	K	L																										AL	AM	AN																																																																																	DQ	DR	DS	DT
6	1	2								10	11	12																										38	39	40																																																																																	121	122		Aux
7
8	Q																																						1																																																																																			1		1
9	W																																						2																																																																																			2		2
10	E										14																												3																																																																																			3		3
11	R																																						4																																																																																			4		4
12	T																																						5																																																																																			1		0
13	Y																																						6																																																																																			2		0
14	A																																						7																																																																																			3		5
15	S																																						8																																																																																			4		0
16	D																																						9																																																																																			1		0
17	F																																						10																																																																																			2		0
CMPLX-DOWN

Cell Formulas
Range	Formula
K10	K10	=SUMIFS(AM8:AM17,DT8:DT17,">0",DR8:DR17,">=3")
DT8:DT17	DT8	=IFERROR(MATCH(A8,'CMPLX-DOWN Pivot'!$G$10:$G$14,0),0)

Or you could use the following array formula, without the helper column:

varios 05abr2025.xlsm
	A	B								J	K	L																										AL	AM	AN																																																																																	DQ	DR
6	1	2								10	11	12																										38	39	40																																																																																	121	122
7
8	Q																																						1																																																																																			1
9	W																																						2																																																																																			2
10	E										14																												3																																																																																			3
11	R																																						4																																																																																			4
12	T																																						5																																																																																			1
13	Y																																						6																																																																																			2
14	A																																						7																																																																																			3
15	S																																						8																																																																																			4
16	D																																						9																																																																																			1
17	F																																						10																																																																																			2
CMPLX-DOWN

Cell Formulas
Range	Formula
K10	K10	=SUM(((IFERROR(MATCH(A8:A17,'CMPLX-DOWN Pivot'!G10:G14,0),0))>0)*(DR8:DR17>=3)*(AM8:AM17))
Press CTRL+SHIFT+ENTER to enter array formulas.

If you don't want the formula in the sheet, you could use the following macro in VBA:

Sub Macro90()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim lr1 As Long, lr2 As Long
  
  Set sh1 = ThisWorkbook.Sheets("CMPLX-DOWN")
  Set sh2 = ThisWorkbook.Sheets("CMPLX-DOWN Pivot")
  lr1 = sh1.Range("A" & Rows.Count).End(3).Row
  lr2 = sh2.Range("G" & Rows.Count).End(3).Row
  
  sh1.Range("K10").Value = Evaluate("=SUM(((IFERROR(MATCH('" & _
    sh1.Name & "'!A8:A" & lr1 & ",'" & _
    sh2.Name & "'!G10:G" & lr2 & ",0),0))>0)*('" & _
    sh1.Name & "'!DR8:DR" & lr1 & ">=3)*('" & _
    sh1.Name & "'!AM8:AM" & lr1 & "))")
End Sub

I don't know if you want the formula in sheet 1 or the pivot sheet. Change this if you want it in the pivot sheet:
sh2.Range("K10").Value

 

[lamarh755]

This type of search cannot be solved simply with the sumifs function, since you must first look for the value in column A in column G of the other sheet, and it may exist in a different row.
You could solve this problem by using an auxiliary column to obtain the result of whether it exists or not, and then you could use the sumifs formula.
For example, if you had these values in the "pivot" sheet:

varios 05abr2025.xlsm
	A					F	G	H
1
9
10							Q
11							W
12							E
13							R
14							A
15
CMPLX-DOWN Pivot

The formula in K10 could be like this:

varios 05abr2025.xlsm
	A	B								J	K	L																										AL	AM	AN																																																																																	DQ	DR	DS	DT
6	1	2								10	11	12																										38	39	40																																																																																	121	122		Aux
7
8	Q																																						1																																																																																			1		1
9	W																																						2																																																																																			2		2
10	E										14																												3																																																																																			3		3
11	R																																						4																																																																																			4		4
12	T																																						5																																																																																			1		0
13	Y																																						6																																																																																			2		0
14	A																																						7																																																																																			3		5
15	S																																						8																																																																																			4		0
16	D																																						9																																																																																			1		0
17	F																																						10																																																																																			2		0
CMPLX-DOWN

Cell Formulas
Range	Formula
K10	K10	=SUMIFS(AM8:AM17,DT8:DT17,">0",DR8:DR17,">=3")
DT8:DT17	DT8	=IFERROR(MATCH(A8,'CMPLX-DOWN Pivot'!$G$10:$G$14,0),0)

Or you could use the following array formula, without the helper column:

varios 05abr2025.xlsm
	A	B								J	K	L																										AL	AM	AN																																																																																	DQ	DR
6	1	2								10	11	12																										38	39	40																																																																																	121	122
7
8	Q																																						1																																																																																			1
9	W																																						2																																																																																			2
10	E										14																												3																																																																																			3
11	R																																						4																																																																																			4
12	T																																						5																																																																																			1
13	Y																																						6																																																																																			2
14	A																																						7																																																																																			3
15	S																																						8																																																																																			4
16	D																																						9																																																																																			1
17	F																																						10																																																																																			2
CMPLX-DOWN

Cell Formulas
Range	Formula
K10	K10	=SUM(((IFERROR(MATCH(A8:A17,'CMPLX-DOWN Pivot'!G10:G14,0),0))>0)*(DR8:DR17>=3)*(AM8:AM17))
Press CTRL+SHIFT+ENTER to enter array formulas.

If you don't want the formula in the sheet, you could use the following macro in VBA:

Sub Macro90()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim lr1 As Long, lr2 As Long
 
  Set sh1 = ThisWorkbook.Sheets("CMPLX-DOWN")
  Set sh2 = ThisWorkbook.Sheets("CMPLX-DOWN Pivot")
  lr1 = sh1.Range("A" & Rows.Count).End(3).Row
  lr2 = sh2.Range("G" & Rows.Count).End(3).Row
 
  sh1.Range("K10").Value = Evaluate("=SUM(((IFERROR(MATCH('" & _
    sh1.Name & "'!A8:A" & lr1 & ",'" & _
    sh2.Name & "'!G10:G" & lr2 & ",0),0))>0)*('" & _
    sh1.Name & "'!DR8:DR" & lr1 & ">=3)*('" & _
    sh1.Name & "'!AM8:AM" & lr1 & "))")
End Sub

I don't know if you want the formula in sheet 1 or the pivot sheet. Change this if you want it in the pivot sheet:
sh2.Range("K10").Value

The value that I got when using the vba code for K10 on the CMPLX-DOWN Pivot screen was 5,081 when it should have been 0 because the value in G10 on the CMPLX-DOWN Pivot screen does not appear in column A of the CMPLX-DOWN sheet at all. I am trying to have K10:K & lastrow fill down with the criteria mentioned when the data in column G10:G and lastrow in CMPLX-DOWN Pivot matches the data in A8:A & LR on the CMPLX-DOWN sheet. There should be multiple values. The images of my sheets are too large to upload for clarity.

 

[lamarh755]

The value that I got when using the vba code for K10 on the CMPLX-DOWN Pivot screen was 5,081 when it should have been 0 because the value in G10 on the CMPLX-DOWN Pivot screen does not appear in column A of the CMPLX-DOWN sheet at all. I am trying to have K10:K & lastrow fill down with the criteria mentioned when the data in column G10:G and lastrow in CMPLX-DOWN Pivot matches the data in A8:A & LR on the CMPLX-DOWN sheet. There should be multiple values. The images of my sheets are too large to upload for clarity.

5,081 is the correct case count value for all of the names combined. I am trying to get those cases broken down by each employee/name.

 

[myall_blues]

You haven't told us where in the formula the bit 'under the cursor' identified as the error is. Nevertheless, as @DanteAmor is telling you, you can't use a SUMIFS formula that way.

The general form of SUMIFS is:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

where sum_range is the range of cells to sum, criteria_range1 is the range that is tested using criteria1, criteria1 is the criteria that defines which cells in criteria_range1 will be added, and so on for subsequent ranges and criteria. Importantly, the criteria are just that, like ">=3.00", "yellow", 99 etc.

If we now look at your code, you've defined your formula as:

SumIfs(rng1, rng2, ">=3.00", rng3, rng4)

You've given sum_range as rng1, criteria_range1 as rng2 and criteria1 as ">=3.00" - so far so good. But then you've defined the 'criteria_range2, criteria2' pair as rng3, rng4 which won't work. rng4 needs to be defined as a criteria. As an example, your formula will end up looking like:

=SUMIFS(A1:A20,B1:B20,">=3.00",C1:C20,D1:D20)

A formula like this might work in a later Excel version using dynamic arrays but the results will be confusing at best - it won't work in your version of Excel.

Another couple of points regarding your code:

1. An important rule for SUMIFS is that the size of all the ranges (sum_range, criteria_range) must be the same. In your case you are defining the bottom of ranges rng1, rng2 and rng3 as eLastRow, but the top of rng4 starts at row 10 and the bottom is set to gLastRow, so it may or may not have the same number of rows as the other 3 (noting that this is a moot point as rng4 must be a criteria).
2. ws1 is set as "CMPLXDOWN Pivot" but wsa is not set, yet gLastRow and rng4 both rely on that value.

I would suggest getting your SUMIFS formula working as a formula in your sheet before you try to convert it into VBA code.

 

[lamarh755]

You haven't told us where in the formula the bit 'under the cursor' identified as the error is. Nevertheless, as @DanteAmor is telling you, you can't use a SUMIFS formula that way.

The general form of SUMIFS is:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

where sum_range is the range of cells to sum, criteria_range1 is the range that is tested using criteria1, criteria1 is the criteria that defines which cells in criteria_range1 will be added, and so on for subsequent ranges and criteria. Importantly, the criteria are just that, like ">=3.00", "yellow", 99 etc.

If we now look at your code, you've defined your formula as:

SumIfs(rng1, rng2, ">=3.00", rng3, rng4)

You've given sum_range as rng1, criteria_range1 as rng2 and criteria1 as ">=3.00" - so far so good. But then you've defined the 'criteria_range2, criteria2' pair as rng3, rng4 which won't work. rng4 needs to be defined as a criteria. As an example, your formula will end up looking like:

=SUMIFS(A1:A20,B1:B20,">=3.00",C1:C20,D1:D20)

A formula like this might work in a later Excel version using dynamic arrays but the results will be confusing at best - it won't work in your version of Excel.

Another couple of points regarding your code:

1. An important rule for SUMIFS is that the size of all the ranges (sum_range, criteria_range) must be the same. In your case you are defining the bottom of ranges rng1, rng2 and rng3 as eLastRow, but the top of rng4 starts at row 10 and the bottom is set to gLastRow, so it may or may not have the same number of rows as the other 3 (noting that this is a moot point as rng4 must be a criteria).
2. ws1 is set as "CMPLXDOWN Pivot" but wsa is not set, yet gLastRow and rng4 both rely on that value.

I would suggest getting your SUMIFS formula working as a formula in your sheet before you try to convert it into VBA code.

When I enter the formula without VBA code, it works. The part that I am trying to get to work is with using dynamic ranges instead of set ranges. The formula below is working because I am defining exact ranges. This report will be ran each day and the number of rows will change, which is why I am trying to go to last row.

When I enter this formula in K10 of the CMPLX-DOWN PIVOT sheet, the correct results flow down to the bottom of the list in column K.
=SUMIFS('CMPLX-DOWN'!AM8:AM248,"CMPLX-DOWN'!DR8:DR248, ">=3.00",'CMPLX-DOWN'!A8:A248,'CMPLX-DOWN Pivot'!G10:G85)

 

[DanteAmor]

he value that I got when using the vba code for K10 on the CMPLX-DOWN Pivot screen was 5,081 when it should have been 0

5,081 is the correct

I don't understand anymore. So the VBA is correct?
In VBA, the range of the last row is dynamic.

The images of my sheets are too large to upload for clarity.

Just provide an example with a sample of your data.
Just like I did.
Use the xl2bb tool to add a minisheet, or rather both minisheets.
Also, manually add the result you expect.

 

[bebo021999]

SUMIFS(...,RANGE1,RANGE2) with criteria_range RANGE2 is a multiple criteria, establish a multiple results

1) In worksheet, using SUMPRODUCT to sum up
=SUMPRODUCT(SUMIFS...))
=SUMPRODUCT(SUMIFS('CMPLX-DOWN'!AM8:AM248,'CMPLX-DOWN'!DR8:DR248,">=3.00",'CMPLX-DOWN'!A8:A248,'CMPLX-DOWN Pivot'!G10:G85))

2) In VBA, 2 altenatives, paste formula or paste value

'paste value
ws1.Range("K10").Value = Evaluate("=SUMPRODUCT(SUMIFS('CMPLX-DOWN'!AM8:AM" & eLastRow & _
        ",'CMPLX-DOWN'!DR8:DR" & eLastRow & ","">=3.00"",'CMPLX-DOWN'!A8:A" & eLastRow & _
        ",'CMPLX-DOWN Pivot'!G10:G" & gLastRow & "))")

'Or, paste formula
ws1.Range("K10").Formula = "=SUMPRODUCT(SUMIFS('CMPLX-DOWN'!AM8:AM" & eLastRow & _
        ",'CMPLX-DOWN'!DR8:DR" & eLastRow & ","">=3.00"",'CMPLX-DOWN'!A8:A" & eLastRow & _
       ",'CMPLX-DOWN Pivot'!G10:G" & gLastRow & "))"

In BOTHway, range "rng" can not use this way, but using sheet range instead.

 

[lamarh755]

Sorry for the delayed response. I had to step away from this project for a little while. Below is the code that worked flawlessly. I don't think I explained it thoroughly enough.

Sub SumFilteredData()
    Dim wsPivot As Worksheet
    Dim wsData As Worksheet
    Dim lastRowPivot As Long, lastRowData As Long
    Dim i As Long
    Dim keyValue As Variant
    Dim resultSum As Double
   
    ' Set references to the worksheets
    Set wsPivot = Sheets("CMPLX-DOWN Pivot")
    Set wsData = Sheets("CMPLX-DOWN")
   
    ' Determine the last used row in the Pivot sheet (column G) and Data sheet (column A)
    lastRowPivot = wsPivot.Cells(wsPivot.Rows.Count, "G").End(xlUp).Row
    lastRowData = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row
   
    ' Loop through each cell in the Pivot sheet range G11:G(lastRowPivot)
    For i = 11 To lastRowPivot
        keyValue = wsPivot.Range("G" & i).Value
       
        ' Use SUMIFS to sum values in AM8:AM(lastRowData) where:
        ' - Sheet "CMPLX-DOWN" Column A equals the key value, and
        ' - Column DR is >= 3.00
        resultSum = Application.WorksheetFunction.SumIfs( _
                        wsData.Range("AM8:AM" & lastRowData), _
                        wsData.Range("A8:A" & lastRowData), keyValue, _
                        wsData.Range("DR8:DR" & lastRowData), ">=3")
                       
        ' Place the result in the Pivot sheet column K at the matching row
        wsPivot.Range("K" & i).Value = resultSum
    Next i
   
    MsgBox "Calculation completed!"
End Sub

 

[Peter_SSs]

@lamarh755
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block at the bottom of this post has more details. I have added the tags for you this time.