none
Sumifs function using date range not workng RRS feed

  • Question

  •      

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

      

    Sub sumifs2()

                         

    this is the criteria
      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

    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.


    ConradfromLA

    Saturday, July 27, 2019 9:07 PM

Answers

  • 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


    ConradfromLA

    • Marked as answer by ConradfromLA 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

    sumifs


    ConradfromLA

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

    =SUMIFS(Others!$T$7:T10,Others!K$7:K10,">="&Others!AD106,Others!K7:K10,"<="&Others!AD108,Others!$W$7:W10,Others!AA103,Others!$T$7:T10,"<>0" )

    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.

    =SUMIFS($T$7:T10,K$7:K10,">="&AD106,K7:K10,"<="&AD108,$W$7:W10,AA103,$T$7:T10,"<>0" )

    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


    ConradfromLA

    • Marked as answer by ConradfromLA Monday, July 29, 2019 3:34 PM
    Monday, July 29, 2019 3:34 PM
  • You're welcome! Glad you found it helpful.

    I guess, you probably selected your own reply as an answer by mistake. ;)


    Subodh Tiwari (Neeraj) sktneer


    Monday, July 29, 2019 3:58 PM
  • Your reply is indeed the answer.  I just don't know how to say answered.

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

    Thanks.

    Conrad


    ConradfromLA

    Monday, July 29, 2019 4:26 PM
  • I have proposed my reply as a proposed answer, see if you get a notification and take a appropriate action.

    Subodh Tiwari (Neeraj) sktneer

    Monday, July 29, 2019 4:33 PM