SUMPRODUCT of values requiring two criteria to be met

trikky

New Member
Joined
Dec 28, 2016
Messages
32
I have spent time looking at the forum and elsewhere to try finding the answer to my question, but have not had any luck. I have attempted to modify solutions found in various sources (including the forums here) but no matter what I try, I get a#VALUE! error. I’ve come close and can get results if I use just one criteria, but not so well with two criteria. I apologise if I’m asking a question alreadyanswered, but I have attempted multiple potential solutions without success.

On my presentation sheet, I want to display the SUM of minutes (in [H:MM] format) spent on a certain task by my staff, with NAME and MONTH being the criteria to be met.

The data source sheet I am getting this information from, will remain closed. Therefore SUMIFS (apparently) won’t work so I have been (unsuccessfully) trying to get SUMPRODUCT to do the trick.

My latest attempt (based on something I found in the forum)is this:
=SUMPRODUCT(--('[Source File]]Sheet1'!$MO:$MO=$A32)*--(' SourceFile]]Sheet1'!$LZ:$LZ=$AJ30)*'[Source File]]Sheet1'!$MN:$MN)


On my source sheet, thecolumns are: MO (employee name), MN (number of minutes) LZ (month name), so the way I look at it, this formula is reading as: Employee name (=A32)*Month Name (=AJ30)*Minutes.

On my presentation sheet, the criteria is A32 (employee name) and AJ30 (month name)

Thank you very much for any assistance you can provide.
 
Last edited:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Your problem statement is too abstract and vague for me to help you. But some observations, none of which is related to the problem.

1. When you post formulas, be sure to copy from the Formula Bar and paste into your posting. The posted SUMPRODUCT formula has some obvious typos. It cannot possibly be an accurate representation of your formula, even if we allow for the (necessarily) abstract reference to "Source File".

2. Never ever use whole-column ranges of the form MO:MO with SUMPRODUCT and with any array expression. It causes Excel to construct and process arrays of 1M+ elements, which is wasteful of memory and CPU time. Your formula causes Excel to consume at least 8MB of memory (probably a lot more) and perform 2M+ comparisons and 3M+ multiplications, and SUMPRODUCT will process 1M+ rows. I presume you have nothing approaching 1M+ rows of data in your external file.

3. Your use of double negate ("--") is redundant. And the third multiplication is unnecessary and inefficient. You should be able to write:
=SUMPRODUCT(('path[file]Sheet1'!$MO$1:$MO$10000=$A32)*('path[file]Sheet1'!$LZ$1:$LZ$10000=$AJ30), 'path[file]Sheet1'!$MN$1:$MN$10000)
or
=SUMPRODUCT(--('path[file]Sheet1'!$MO$1:$MO$10000=$A32), --('path[file]Sheet1'!$LZ$1:$LZ$10000=$AJ30), 'path[file]Sheet1'!$MN$1:$MN$10000)

4. In fact (wild speculation), #3 might fix your #VALUE problem. Your use of "multiply by MN:MN" might be the source of the #VALUE error. Typically, at least MN1 is a column title (text) that cannot be interpreted as a number. "Multiply by non-numeric text" causes a #VALUE error.

5. Finally, if MN:MN contains Excel time (I would not call it "minutes" if it is entered as h:mm), I suggest that you format the SUMPRODUCT cell as Custom [h]:mm or [m] or something similar. The point is, "[h]" displays time greater than 23 hours, and "[m]" displays time greater than 59 minutes.
 
Last edited:
Upvote 0
I used different columns and different references.
I restricted the columns to 20 rows i.e. not full columns.


Excel 2010
B
100:30:00
1d
Cell Formulas
RangeFormula
B1=SUMPRODUCT(--('[Source File.xlsm]Sheet1'!$A$2:$A$20=A32),--('[Source File.xlsm]Sheet1'!$C$2:$C$20=B32),('[Source File.xlsm]Sheet1'!$B$2:$B$20))


With the Source File closed, on my system the above formula looks like

=SUMPRODUCT(--('D:\Excel14\[Source File.xlsm]Sheet1'!$A$2:$A$20=A32),--('D:\Excel14\[Source File.xlsm]Sheet1'!$C$2:$C$20=B32),('D:\Excel14\[Source File.xlsm]Sheet1'!$B$2:$B$20))

It may be a idea to create a similar small file and check the construction; then adapt to your actual workbook.
 
Last edited:
Upvote 0

A
B
..
AJ
AK
30
Full Name
Short Name
September
(Merged with AJ & AL)
31

32
John Smith
John
#VALUE!
33
Bill Reid
Bill

34
Tiger Woods
Tiger


<tbody>
</tbody>
Your problem statement is too abstract and vague for me to help you.

Hi. Sorry, while I was intentionally simplistic in what I wrote and I can see how it may seem abstract, but I didn’t think it was vague.I need to sum X, with Y and Z as the criteria to be met.

In the event it helps clarify my requirements, here’s a re-write: On my presentation sheet in cell AK32, I want to display the sum of minutes (in [H:MM]), for a specific month (specified in AJ30) AND specific person (specified in A32)

The source file will remain closed, which is why I am going the SUMPRODUCT route.The data columns are:


  • MN (Minutes, formatted as General)eg: 180
  • LZ (Month name in text, formatted as General) eg: September
  • MO (Staff name in text, formatted as General) eg: John Smith
1. When you post formulas, be sure to copy from the Formula Bar and paste into your posting. The posted SUMPRODUCT formula has some obvious typos. It cannot possibly be an accurate representation of your formula, even if we allow for the (necessarily) abstract reference to "Source File".
Sorry for the typos. I did indeed copy the formula from the bar - the typos to which you refer were simply the result of converting the actual filename to something more generic and copying that across multiple occurrences.Unfortunately the typing into this website has been a little odd (skipping key presses, etc), so I typically type my text externally, then Paste into the forum.

2. Never ever use whole-column ranges of the form MO:MO with SUMPRODUCT and with any array expression. It causes Excel to construct and process arrays of 1M+ elements, which is wasteful of memory and CPU time. Your formula causes Excel to consume at least 8MB of memory (probably a lot more) and perform 2M+ comparisons and 3M+ multiplications, and SUMPRODUCT will process 1M+ rows. I presume you have nothing approaching 1M+ rows of data in your external file.
Great tip, thanks. I’ll narrow the ranges down to 2-3k instead.

3. Your use of double negate ("--") is redundant.
Thanks also for the tip here. As outlined in my original post, I was copying a potential solution that I had found in other searches in this forum, since it was suggested by someone in a reply, so included everything, including the double negates.

Taking into consideration your comments, here is a revised formula copied directly (with the filename changed of course). Still gets me the value error. I suspect the text words (month, name) have something to do with that, which is why I’m posting here to get some help from those more knowledgeable than I.

=SUMPRODUCT(('[Filename.xlsb]SPdata'!$MO$3:$MO$2000=$A32)*('[Filename.xlsb]SPdata'!$LZ$3:$LZ$2000=$AJ30),'[Filename.xlsb]SPdata'!$MN$3:$MN$2000)
 
Last edited:
Upvote 0
Sorry for the typos. [....] typing into this website has been a little odd (skipping key presses, etc)

I'm so-o happy to hear someone else say that. I've mentioned it to the moderators, but they seem to be unfamiliar with the problem. Perhaps you and I should compare notes to see if we can isolate the conditions that expose this problem. Send me a PM. FYI, I work around it by clicking the A/A button and switching to Source Mode.
 
Upvote 0
Did you read post #3 ?

Hi, Dave

After the other provided solution didn’t work, I tested yours.
Initially didn’t work, but when tested on a shorter sheet (with the range entered directly by selection on the source workbook) it did work.

Transfer it to the full sheet and it didn’t work again, however after the discovery of a #VALUE ! Error in the month column (LZ) further down the sheet.
Taking care of that, everything was A-OK.

So, here’s my final formula, including the conversion from text number of minutes (such as 75) to [h]:mm .

=IF(A32="","",SUMPRODUCT(--([SourceFile]SPdata!$MO$3:$MO$1000=$A32),--([SourceFile]SPdata!$LZ$3:$LZ$1000=AJ$1),([SourceFile]SPdata!$MN$3:$MN$1000))/(24*60))

Thank you for your solution.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top