Use of SUMPRODUCT with multiple criteria

[ron2k]

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

 

[JASONW10]

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]

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]

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]

You could do it like this

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

 

[Aladin Akyurek]

Try...

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

=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]

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]

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]

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]

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...