Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    I have used SUMIF a fair bit and so think I know the syntax. I'm trying to use it for something very simple now (summing values by month, using 1 for Jan, 2 for Feb etc as criteria) , but am encountering what appear to be errors which I can't explain. What I'm trying to do and the problems I'm encountering are made in clear in the attached simple spreadsheet.
    Can anyone advise? I'd be very grateful.

    regards

    lingyai
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Your formula should be:

    =SUMIF(B$7:B$246, Q7, D$7$246)

    not:

    =SUMIF(B$7:D$246, Q7, D$7$246)

    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    Ah, so.... thanks Rory

    (strange how it worked in the other case though)

    regards

    lingyai

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    By including three columns in the criteria range, Excel includes three columns in the data range too. In your sample for month 2, the number 2 appears a few times in column D (which is included in the criteria range) so the formula includes any values in those rows in column F, which happens to contain years in the first few rows. In D11 and D24 for example you have 2, and in F11 and F24 you have 2019 and 2032 respectively, hence your total is 4051 higher than expected.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    Thanks Rory. Guess I didn't know the syntax after alll...

Posting Permissions

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