# Sumifs function using date range not workng

• ### Question

•

I have this formula using date range on sumifs, but did not result to what I want

Sub sumifs2()

Dim VT, VK, VW   As String

VT = "T10"
VK = "K10"
VW = "W10"

Sheets("Others").Select
Sheets("Others").Range("AE108").Formula = _
"=SUMIFS(Others!\$T\$7:" & VT & ",Others!K\$7:" & VK & ","">=""&Others!AD106 ,Others!K7:" & VK & ",""<=""&Others!AD108,Others!\$W\$7:" & VW & ",Others!AA103,Others!\$T\$7:" & VT & ",""<>0"" )"

End Sub

This is the data

﻿Can anybody review why I am not getting any result. There are only 4 lines of data, yet the result is zero.

Saturday, July 27, 2019 9:07 PM

• Thanks for the reply. I did what you said and realized that the date on the data is not a  date. Thus, it is creating an error. What you said saves me a lot of checking for the error.

Thanks

• Marked as answer by Monday, July 29, 2019 3:34 PM
Monday, July 29, 2019 3:34 PM

### All replies

• This is the sumifs

﻿

Thanks

Sub sumifs2()

Dim VT, VK, VW   As String

VT = "T10"
VK = "K10"
VW = "W10"

Sheets("Others").Select
Sheets("Others").Range("AE108").Formula = _
"=SUMIFS(Others!\$T\$7:" & VT & ",Others!K\$7:" & VK & ","">=""&Others!AD106 ,Others!K7:" & VK & ",""<=""&Others!AD108,Others!\$W\$7:" & VW & ",Others!AA103,Others!\$T\$7:" & VT & ",""<>0"" )"

End Sub

Any help will be much appreciated

Saturday, July 27, 2019 10:00 PM
• Your VBA formula would be translated into this...

Is it correct as per the formula logic?

Also when you already select the Others Sheet before placing the formula, you can exclude the Sheet reference from the formula as all the ranges referenced in the formula belong to the Others Sheet only. e.g.

What SUMIFS formula do you actually try on the sheet itself which returns the correct output?

You should first finalize the formula on the sheet itself and then try to place it through VBA.

The screenshots you shared don't display all the references on the sheet.

If you are unable to post the One drive or Google drive link of your file, please hide the unused rows and columns (i.e. rows and columns which are not relevant to the formula) and post another screenshot which should include the row and column headers also along with the data in all the ranges used in the formula.

Subodh Tiwari (Neeraj) sktneer

Sunday, July 28, 2019 4:30 AM
• Thanks for the reply. I did what you said and realized that the date on the data is not a  date. Thus, it is creating an error. What you said saves me a lot of checking for the error.

Thanks

• Marked as answer by Monday, July 29, 2019 3:34 PM
Monday, July 29, 2019 3:34 PM

Subodh Tiwari (Neeraj) sktneer

Monday, July 29, 2019 3:58 PM

Thanks for the help. It is very much appreciated- it avoided me spinning my wheels.

Thanks.