+ Reply to Thread
Results 1 to 3 of 3

SumIf/SumProduct Formula Help

  1. #1
    Jacinthe
    Guest

    SumIf/SumProduct Formula Help

    I have a worksheet containing the following:
    Name ID Mon Tue Wed Thur Fri Sat Sun Avg

    For each ID, there may be multiple listings of the same name, because each
    day has to have its own row. What I want to do is create a formula that will
    only read the "Mon" information for each name OR the "avg" line, if there is
    no Mon. Our current function is a simple =sumif using the name and the
    monday line, but it requires us to do some additional work to add on the
    "avg" line after the printout is complete.

    I think the sumproduct function might be useful here, but I'm not sure how
    to apply it in this situation ... any ideas?

  2. #2
    Bob Phillips
    Guest

    Re: SumIf/SumProduct Formula Help

    If ID is in B, Mon In C, Avg in J, then try

    =IF(B2:B10="a",SUM(IF(C2:C10<>"",C2:C10,J2:J10)))

    which is an array formula, so commit with Ctrl-Shift-Enter

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Jacinthe" <[email protected]> wrote in message
    news:[email protected]...
    > I have a worksheet containing the following:
    > Name ID Mon Tue Wed Thur Fri Sat Sun Avg
    >
    > For each ID, there may be multiple listings of the same name, because each
    > day has to have its own row. What I want to do is create a formula that

    will
    > only read the "Mon" information for each name OR the "avg" line, if there

    is
    > no Mon. Our current function is a simple =sumif using the name and the
    > monday line, but it requires us to do some additional work to add on the
    > "avg" line after the printout is complete.
    >
    > I think the sumproduct function might be useful here, but I'm not sure how
    > to apply it in this situation ... any ideas?




  3. #3
    Jacinthe
    Guest

    Re: SumIf/SumProduct Formula Help

    I found a formula that works even smoother - I'm not sure why, but here it is:

    =IF(SUMIF($B$8:$B$4140,A5,$L$8:$L$4140)=0,SUMIF($B$8:$B$4140,A5,$Z$8:$Z$4140),SUMIF($B$8:$B$4140,A5,$L$8:$L$4140))

    In this formula, A is the reference name, B is the name in the worksheet, L
    is the Monday column, and Z is the Average column. Thanks so much for your
    help!

    "Bob Phillips" wrote:

    > If ID is in B, Mon In C, Avg in J, then try
    >
    > =IF(B2:B10="a",SUM(IF(C2:C10<>"",C2:C10,J2:J10)))
    >
    > which is an array formula, so commit with Ctrl-Shift-Enter
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1