Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: SUMPRODUCT not working

  1. #1

    SUMPRODUCT not working



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi

    Please help as my formula is not working ! I have attached a spreadsheet, and M18 is the start of my formula. Basically I am trying to add all my timesheet information that is against a job number, and a task - to add the amount of hours together to see if we are over/under budget.

    I have attached an example, if you could please help me.

    Thanks heaps
    Benjisumproduct error.xlsx

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    681
    Articles
    0
    Excel Version
    Excel 2010 64bit
    In M18 try

    =SUMPRODUCT(($K$2:$K$13=$A18)*($M$2:$M$13=M$17)*($L$2:$L$13))

  3. #3
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Hello
    You could just add the double uniary after the first bracket in M18 (As below). Do the same in M19 to M25, and make sure you fix the cell addresses with $s so that L2 to L13 doesn't shift when you copy the formula down or across.

    =SUMPRODUCT(--($K$2:$K$13=$A18),($M$2:$M$13=M$17)*($L$2:$L$13))

  4. #4
    Why would you mix up doubly unary operators and multiplication operators in one SUMPRODUCT formula?

    But more importantly, this problem doesn't need SUMPRODUCT, SUMIFS will do it perfectly well and is much more efficient

    =SUMIFS(L2:L13,K$2:K$13,$A18,$M2:$M13,M$17)

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    681
    Articles
    0
    Excel Version
    Excel 2010 64bit
    I may have interpreted the OP wrong, but think they were looking for a formula that could be put in cell M18 and dragged across the columns and down the rows.

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    @Bob Phillips
    I didn't question the use of SUMPRODUCT because many people are still using older versions of Excel that don't have the SUMIFS function. In Cell M18, the result was wrong because there were two logical tests and without the double unary, 0 is returned when both were TRUE. I'm not saying its the only solution, but it works.

  7. #7
    @Hercules1946,
    The OP's file is an .xlsx, so they clearly have a post-2003 Excel.

    My other point was that your formula was using both -- and *. Whenever I see that I feel that the poster does not properly understand how SUMPRODUCT works. Whether you use -- or * is almost immaterial (although there are some circumstances where you have to use one, some where you have to use the other), but at least be consistent.

  8. #8
    Quote Originally Posted by NoS View Post
    I may have interpreted the OP wrong, but think they were looking for a formula that could be put in cell M18 and dragged across the columns and down the rows.
    Which one can't?

  9. #9
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    @NoS
    I don't think the formula can be copied as you suggest, because where there are multiple timesheets for one code (e.g. M18) the formula needs two logical tests, whereas for others only one is required.
    That said, it should be possible to create 2 or 3 variations that can be copied to other cells with matching requirements.

  10. #10
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Having looked at the example again, bearing in mind that you want to build a matrix of code x task hours the formula in M18 (once finalised) should be copied into M18:V25 to complete the matrix table as it stands. Ive done this in my attachment, which doesn't have many "hits" because there are only a few cases of common codes and tasks in both data groups at this stage.
    Its up to you which function you use. I suppose Im a bit biased because in my workplace we had lots of people with different versions of Excel, and COUNTIFS and SUMIFS in particular created a lot of problems when the users started to exchange spreadsheets between each other.

    HTH
    Hercules
    Attached Files Attached Files

Page 1 of 2 1 2 LastLast

Posting Permissions

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