SUMPRODUCT not working

benji8798

New member
Joined
Aug 8, 2013
Messages
2
Reaction score
0
Points
0
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
BenjiView attachment sumproduct error.xlsx
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
In M18 try

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

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
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))
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
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
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
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
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
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. :eek:
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
@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
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
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
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
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.
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
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
 

Attachments

  • sumproduct error1.xlsx
    14 KB · Views: 27

benji8798

New member
Joined
Aug 8, 2013
Messages
2
Reaction score
0
Points
0
Thanks

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