Use of SUMPRODUCT with multiple criteria

ron2k

Board Regular
Joined
Jan 28, 2008
Messages
139
Season's greetings

Hi guys I have this sumproduct formula on several cells:
=SUMPRODUCT(--('Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$A$79=AI18),'Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$Z$8:$Z$79)+SUMPRODUCT(--('Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$A$79=AK18),'Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$Z$8:$Z$79)+SUMPRODUCT(--('Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$A$79=AM18),'Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$Z$8:$Z$79)

It is basically looking for three criteria on one common column and returning the value which are on the same column as well. I did not use VLOOKUP for fear of getting an N/A error.

My request is whether this can be condensed. It is quite long, and I am planning to use it with another set of arguments, and in excel2003 we only 1,024 characters I believe. So any suggestions.

Thank you
 
Last edited:

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

JASONW10

Board Regular
Joined
Mar 11, 2005
Messages
231
You can stop a n/a by using a iserror as part of your lookup

something + if(iserror(vlookup(something)),0,vloopup(something))
I hope this is understandable if not I will go into more detail. Just remember if the look up comes back with a n/a it will show a 0 value to be added and if it did find something then it would add it.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
One common and very simple method to reduce the length of formulas...

If your formula has something repeated several times, use a named range...

Your fomrmula uses these 2 ranges 3 times each
'Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$A$79
'Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$Z$8:$Z$79

So, click Insert - Name - Define.
Give it a name, say MyRange for example
In the Refers to box, put
='Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$A$79
Click Add

Now add another name - say MyRange2
Refers to
='Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$Z$8:$Z$79

Now your formula can be

=SUMPRODUCT(--(MyRange=AI18),MyRange2)+SUMPRODUCT(--(MyRange=AK18),MyRange2)+SUMPRODUCT(--(MyRange=AM18),MyRange2)


You can definately be creative with the names, so they make sense to you.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
I also have an idea to combine all 3 sumproducts into 1...

But, what is in AJ18 and AL18 ?
Anything that would throw it off if the formula were to look in the whole range, AI18:AM18?

I mean, is anything in those 2 cells that would be a match within the range
'Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$A$79
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

You could do it like this

=SUMPRODUCT((MyRange=AI18)+(MyRange=AK18)+(MyRange=AM18),MyRange2)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Try...

Control+shift+enter, not just enter...

Rich (BB code):
=SUM(IF(ISNUMBER(MATCH(
   'Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$A$79,
      IF(MOD(COLUMN(AI18:AM18)-COLUMN(AI18),2)=0,AI18:AM18),0)),
   'Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$Z$8:$Z$79))
 

ron2k

Board Regular
Joined
Jan 28, 2008
Messages
139

ADVERTISEMENT

I also have an idea to combine all 3 sumproducts into 1...

But, what is in AJ18 and AL18 ?
Anything that would throw it off if the formula were to look in the whole range, AI18:AM18?

I mean, is anything in those 2 cells that would be a match within the range
'Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$A$79

In AJ18 and AL118 I have spaces - Just an empty column between the three search criteria columns. And it is possible that on few cells in column AI, AK, AM, would be blank. Thats the reason why I use SUMPRODUCT and not VLOOKUP, and given that even on these columns I would have empty cells I don't think it will really affect the end product as so far it is working.

In addition, naming ranges will be quite a thing. Because if you notice it is pulling info from this file: SC2 Apr09 - Mar 10 Actuals.xls. I have one file like that for five years prior to that. And that formula I am pulling infor for one sheet withing that file. I have 12 identical sheets withing each workbook. And that's the reason why I was thinking on using the "Pull" UDF. Please take a look at this unanswered thread as well, and let me know what you think? http://www.mrexcel.com/forum/showthread.php?p=2157617#post2157617

Thank you.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
In AJ18 and AL118 I have spaces - Just an empty column between the three search criteria columns.

In that case...

=SUMPRODUCT(--(ISNUMBER(MATCH('Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$A$79,F1:F5,0))),'Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$Z$8:$Z$79)


In addition, naming ranges will be quite a thing.

In the long run, It would be well worth the time and effort....just sayin...
 

ron2k

Board Regular
Joined
Jan 28, 2008
Messages
139
In that case...

=SUMPRODUCT(--(ISNUMBER(MATCH('Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$A$79,F1:F5,0))),'Q:\5yr Branch review\Balance Sheet\[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$Z$8:$Z$79)




In the long run, It would be well worth the time and effort....just sayin...

Thank you. I agree with you with the naming of sheets. It's going to be - 12 identical sheets on 5 files. I guess, given that, for instance all 5 workbooks contain the tab "bze" I can name the range on bze tab in 09-10 file "BZE2010" and the on 08-09 file BZE20009, right? What do you think. INDIRECT still can't work with ranges on closed workbooks right. I guess I have to still have to revert to "Pull". (On a similar note can you help me with the other thread I referred you to, please...)

Thank you.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
So, 2 ranges (A8:A79 and Z8:Z79), on 12 sheets, in 5 books..
that's 120 named ranges
Not terrible, I've seen much much worse...

If it takes 30 seconds to create each one, that's 60 minutes - 1 hour.

I suggested using named ranges 1 hour and 30 minutes ago....
Could have been done by now...

Seriously, it's worth the time and effort.


Edit

Anyway, you can cut the number of ranges in half by only naming the A8:A79, then in your formulas using Offset(name,0,25)

That's not true, sorry. Offset doesn't work on closed books either...
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,992
Members
414,115
Latest member
SFUser

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
Top