sumifs with date and text criteria
Results 1 to 10 of 10

Thread: sumifs with date and text criteria
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2008
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default sumifs with date and text criteria

    Microsoft Excel 2007:



    I have the following formula in J45, which gives wrong answer as 0.
    =SUMIFS(D5:D33,C5:C33,">="&H40,E5:E33,"<="&H41,G5:G33,"TaxSave")



    D5:D33 Amount in numericals
    C5:C33 Start date in dd-mm-yyyy format
    E5:E33 End date in dd-mm-yyyy format
    G5:G33 Text msg, (example: Current Old TaxSave)
    H40 Helper with Start date (example 01-04-2018)
    H41 Helper with End date (example 31-03-2019)
    End date is greater than Start date.



    In my worksheet I have text TaxSave in G14 only.



    Kindly correct the formula suitably.

    Thanking you,

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: sumifs with date and text criteria

    The formula looks correct.

    What do you get with:

    =COUNT(D5:D33)

    =COUNT(E5:E33)

    =COUNT(E5:E33)

    +COUNTIFS(G5:G33,"TextSave")
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    Board Regular
    Join Date
    May 2008
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sumifs with date and text criteria

    Microsoft Excel 2007
    =count(D5:D33) Answer 23
    =count(C5:C33) Answer 23
    =count(E5:E33) Answer 23
    =count(E5:E33)+countif(G5:G33,"TaxSave") Answer 24

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: sumifs with date and text criteria

    Quote Originally Posted by Muthukrishnan V View Post
    Microsoft Excel 2007
    =count(D5:D33) Answer 23
    =count(C5:C33) Answer 23
    =count(E5:E33) Answer 23
    =count(E5:E33)+countif(G5:G33,"TaxSave") Answer 24
    Sorry, I meant just:

    =
    COUNTIF(G5:G33,"TextSave")

    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    Board Regular
    Join Date
    May 2008
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sumifs with date and text criteria

    =COUNTIF(G5:G33,"TaxSave") Answer 1

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: sumifs with date and text criteria

    Quote Originally Posted by Muthukrishnan V View Post
    =COUNTIF(G5:G33,"TaxSave") Answer 1
    Is this count what you would expect to obtain?
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    Board Regular
    Join Date
    May 2008
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sumifs with date and text criteria

    As requested in my first post, I want to sum from Column D; with start date condition from Column C;
    with End date condition from column E; and text condition from Column G.
    Kindly refer formula in my first post.
    InG14 only text Taxsave. C14 date 30-07-2018. D14 is amount 54718. Correct answer: 54718. But I get 0 answer.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: sumifs with date and text criteria

    Quote Originally Posted by Muthukrishnan V View Post
    As requested in my first post, I want to sum from Column D; with start date condition from Column C;
    with End date condition from column E; and text condition from Column G.
    Kindly refer formula in my first post.
    InG14 only text Taxsave. C14 date 30-07-2018. D14 is amount 54718. Correct answer: 54718. But I get 0 answer.
    If G14 = TaxSave, C14 = 30-07-2015, D14 = 54718, AND

    =G14 = "TaxSave" yields TRUE

    =ISNUMBER(C14) yields TRUE

    =(C14 >= "30-07-2018"+0) * (C14 <= "30-07-2018"+0) yields 1

    =ISNUMBER(D14) yields TRUE

    then you should get: 54718 with the formula you already have.
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    Board Regular
    Join Date
    May 2008
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: sumifs with date and text criteria

    Thank you Sir, Great!

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,113
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: sumifs with date and text criteria

    Quote Originally Posted by Muthukrishnan V View Post
    Thank you Sir, Great!
    You are welcome.
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •