# SUMPRODUCT not working

#### benji8798

##### New member
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
BenjiView attachment sumproduct error.xlsx

#### NoS

##### New member
In M18 try

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

#### Hercules1946

##### New member
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))

#### Bob Phillips

##### Super Moderator
Staff member
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)

#### NoS

##### New member
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.

#### Hercules1946

##### New member
@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.

#### Bob Phillips

##### Super Moderator
Staff member
@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.

#### Bob Phillips

##### Super Moderator
Staff member
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?

#### Hercules1946

##### New member
@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.

#### Hercules1946

##### New member
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

#### Attachments

• sumproduct error1.xlsx
14 KB · Views: 27

#### NoS

##### New member
Which one can't?

Correct me if I'm mistaken, but for the SUMIFS formula posted to be drag-able and yield correct results, wouldn't it need about eight more dollar signs?

@Nos
Agreed.

#### benji8798

##### New member
Thanks

Thanks for all your help. Formula is now working. Cheers