Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: sum & sumif questions

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sum works properly in the following instance:

    =sum(j8:m120)

    This sums all the values in between those two cells.

    However in a sumif function:

    =sumif(b8:b120,CONDITION,j8:m120)

    I get some whacked out answer. It's not the sumer of the cells. Why does that (j8:m12) work in the sumif, but does work in the sum function?

    Thanks

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Confused as to what you are trying to do. Please clarify.

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The condition is ">"&today()-30*x where x is the number of months.

    Well this is for a pilot log book

    Column B contains dates
    Columns J through M contain flight times.

    I have an equation to total the times for the past x months (1, 6, & 12).

    The following works and returns the correct totals:

    =SUMIF(B7:B9999,">"&TODAY()-30,J7:J9999)
    +SUMIF(B7:B9999,">"&TODAY()-30,K7:K9999)
    +SUMIF(B7:B9999,">"&TODAY()-30,L7:L9999)
    +SUMIF(B7:B9999,">"&TODAY()-30,M7:M9999)

    Howerver this doesn't work:

    =SUMIF(B7:B9999,">"&TODAY()-30,J7:M9999)


    EDIT: Formatting

    [ This Message was edited by: Ice5 on 2002-04-01 12:56 ]

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    When you enter your formula, are you entering it by pressing Ctr+shift+enter instead of just enter. Look up how to enter an array formula (CSE formula).
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I hadn't tried CTRL + SHIFT + ENTER.

    I tried it now however, and same deal.

    i.e.

    =SUMIF(B8:B10000,">"&TODAY()-30*6,J8:M10000)

    Still does not return the correct value (it returns 0.9 instead of 21.5)

    [ This Message was edited by: Ice5 on 2002-04-01 13:04 ]

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ice,

    your ranges are unequal size....

    see in your individual sumifs, they are all equal in size so individually you get correct results

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,646
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-01 12:53, Ice5 wrote:
    The condition is ">"&today()-30*x where x is the number of months.

    Well this is for a pilot log book

    Column B contains dates
    Columns J through M contain flight times.

    I have an equation to total the times for the past x months (1, 6, & 12).

    The following works and returns the correct totals:

    =SUMIF(B7:B9999,">"&TODAY()-30,J7:J9999)
    +SUMIF(B7:B9999,">"&TODAY()-30,K7:K9999)
    +SUMIF(B7:B9999,">"&TODAY()-30,L7:L9999)
    +SUMIF(B7:B9999,">"&TODAY()-30,M7:M9999)

    Howerver this doesn't work:

    =SUMIF(B7:B9999,">"&TODAY()-30,J7:M9999)


    EDIT: Formatting

    [ This Message was edited by: Ice5 on 2002-04-01 12:56 ]
    SUMIF requires ranges of equal size. Thus,

    =SUMIF(B7:B9999,">"&TODAY()-30,J7:M9999)

    cannot replace your larger formula that adds up the results of independent SUMIFs.

    If you insist on a single formula, use:

    =SUMPRODUCT((B7:B9999>TODAY()-30)*(J7:M9999))

    Aladin


  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Oh, neat.

    Thanks Aladin.

    I don't REQUIRE only one formula, but otherwise I'd have 10 separate forumla's. Just ugly and hard to work on.

    the SUMPRODUCT function works out perfectly though, thanks.

Some videos you may like

User Tag List

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
  •