1. ## SUMPRODUCT not working

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.

Thanks heaps
Benjisumproduct error.xlsx

2. In M18 try

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

3. 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. 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. 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. @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. @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. Originally Posted by NoS
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. @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. 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

Page 1 of 2 1 2 Last

#### Posting Permissions

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