Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Posts
    133

    Unanswered: newbie: sum only for the numbers of sunday

    My spreadsheet is as follow:
    First row:
    a1 is sun
    a2 is mon
    a3 is tue
    and so on etc

    second row is numbers:
    b1 is 1.4
    b2 is 13
    and so on
    My question is how can I sum the second row for the numbers which have cell in first row is sun only.
    Please help me. I need your help. Many thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    For this example i would use a SUMIF

    something like this

    =SUMIF(A1:A3,"sun",B1:B3)
    have a look at the help file for more information

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    SUMIF works great for one condition. If you have multiple conditions, use SUMPRODUCT

    =SUMPRODUCT(($A$1:$A$10="sun")*($B$1:$B$10="East") *($C$1:$C$10="First Qtr"),$D$1:$D$10)

    This will look at only those on sun in column A, which also has only items for East in column B, and First Qtr for column C, then sums up those values in column D.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    For the sake of total accuracy you should use -- as well in your sumproduct

    i.e.

    =SUMPRODUCT(--(A1:A3="sun"))
    will work for a count but
    =SUMPRODUCT((A1:A3="sun"))
    wont

    this is only needed for single arrays rather than in the above example
    as Boolean multiplication does lead to integers

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Good point, but that is also why I don't use SUMPRODUCT on single arrays.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    I cant believe im making comments on the man who i leant to use this function from in the first place,
    i use -- just to make sure im not going to get any errors, and i thought i should point it out, in case cuongvt asks why the SUMPRODUCT function isnt working

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091


    In my old age, I tend to get sloppy! Thanks for the reminders.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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