Array to Sumproduct excluding errors multi conditions

ajones

Board Regular
Joined
Oct 26, 2002
Messages
104
I have a sum(if multi conditional array that I am doing many times and bogging things down.

I am getting better at converting array formulas to sumproducts, but this one seems to be extra difficult. The table i am working with has #N/A and or other errors to exclude also.

I can get similar sumproduct array formulas to work, but not this one. Any suggestions?


array formula {=SUM(IF( ( (LEFT('Working Sheet'!$A$1:$A$20000,2)= "51") + (LEFT('Working Sheet'!$A$1:$A$20000,2)= "52") + (LEFT('Working Sheet'!$A$1:$A$20000,2)= "53") ) * NOT(ISERROR('Working Sheet'!N$1:N$20000) ),('Working Sheet'!N$1:N$20000),"-")) }

The Sheet 'Working Sheet' A1:a20000 has a 4 digit number that I need to match the first 2. Due to other constraints I was trying not to create a new column with just the 2 digits of the 4 digit number.


The closes sumproduct I came up with was:
=SUMPRODUCT( ( --(LEFT('Working Sheet'!$A$1:$A$20000,2)= "51") + --(LEFT('Working Sheet'!$A$1:$A$20000,2)= "52") + --(LEFT('Working Sheet'!$A$1:$A$20000,2)= "53") ) * --NOT(ISERROR('Working Sheet'!N$1:N$20000) ),('Working Sheet'!N$1:N$20000))

the return is #N/A

Any suggestions for for a sumproduct or other steps to make optimize and make more efficient would be appreciated.


Thanks

Alan


P.S. If anyone has a a suggestion on using index or better function that is not volatile (faster) instead of using indirect to reference other sheets (or even files) as in
=INDIRECT("'" & B1& "'"&"!" & "A1")
=INDIRECT("'" & B1& "'"&"!" & C1)

please check out
http://www.mrexcel.com/board2/viewtopic.php?t=134225&start=0&postdays=0&postorder=asc&highlight=
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi ajones

The IF in your first formula does two different functions

- sets conditions, the 51-53 filter

- traps errors in 'Working Sheet'!N$1:N$20000

There's no problem in converting conditions like the ones I mention first between sum and sumproduct. They are both array formulas but since sumproduct is a native array formula, like mmult and others you don't have to confirm with ctrl+shift+del. Also it's syntax is great to individualize each condition.

Now the second line, the error trapping is another story. The IF allows you to test if the value in the cell is an error AND ONLY IF IT'S NOT AN ERROR will you perform the operation, in this case just getting the cell value.

You cannot use sumproduct in this case because sumproduct always multiplies all the parameters. In this case, in your second formula you would always multiply

NOT(ISERROR('Working Sheet'!N$1:N$20000) ) * ('Working Sheet'!N$1:N$20000)

So, when 'Working Sheet'!N$1:N$20000 is error you detect it but then multiply the value of the test by the error value, producing an error.

I may be wrong but I'm convinced that you can't get rid of the IF in this case, and so you are stuck to the SUM.

Hope this helps
PGC

P. S. Of course, if you wanted you could just replace the sum by sumproduct in the first formula, but since you are using extra array functionality you would still have to confirm with ctrl+shift+del, and there's no point in doing it.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,705
array formula {=SUM(IF( ( (LEFT('Working Sheet'!$A$1:$A$20000,2)= "51") + (LEFT('Working Sheet'!$A$1:$A$20000,2)= "52") + (LEFT('Working Sheet'!$A$1:$A$20000,2)= "53") ) * NOT(ISERROR('Working Sheet'!N$1:N$20000) ),('Working Sheet'!N$1:N$20000),"-")) }

The above formula can be shortened as follows...

=SUM(IF(ISNUMBER(MATCH(LEFT('Working Sheet'!$A$1:$A$20000,2),{"51","52","53"},0)),IF(ISNUMBER('Working Sheet'!N$1:N$20000),'Working Sheet'!N$1:N$20000)))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

Hi again

You can also simplify your formula like this:

=SUM(IF((LEFT('Working sheet'!$A$1:$A$20000,2)={"51","52","53"})*(ISNUMBER('Working sheet'!N$1:N$20000)),'Working sheet'!N$1:N$20000))

HTH
PGC

The laternative you propose is expensive. The equlaty test leads inevitably to vector X matrix, a multiplication instead of faster IF-filtering.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

I have a sum(if multi conditional array that I am doing many times and bogging things down.

I am getting better at converting array formulas to sumproducts, but this one seems to be extra difficult. The table i am working with has #N/A and or other errors to exclude also.

I can get similar sumproduct array formulas to work, but not this one. Any suggestions?


array formula {=SUM(IF( ( (LEFT('Working Sheet'!$A$1:$A$20000,2)= "51") + (LEFT('Working Sheet'!$A$1:$A$20000,2)= "52") + (LEFT('Working Sheet'!$A$1:$A$20000,2)= "53") ) * NOT(ISERROR('Working Sheet'!N$1:N$20000) ),('Working Sheet'!N$1:N$20000),"-")) }

The Sheet 'Working Sheet' A1:a20000 has a 4 digit number that I need to match the first 2. Due to other constraints I was trying not to create a new column with just the 2 digits of the 4 digit number.


The closes sumproduct I came up with was:
=SUMPRODUCT( ( --(LEFT('Working Sheet'!$A$1:$A$20000,2)= "51") + --(LEFT('Working Sheet'!$A$1:$A$20000,2)= "52") + --(LEFT('Working Sheet'!$A$1:$A$20000,2)= "53") ) * --NOT(ISERROR('Working Sheet'!N$1:N$20000) ),('Working Sheet'!N$1:N$20000))

the return is #N/A

Any suggestions for for a sumproduct or other steps to make optimize and make more efficient would be appreciated.


Thanks

Alan


P.S. If anyone has a a suggestion on using index or better function that is not volatile (faster) instead of using indirect to reference other sheets (or even files) as in
=INDIRECT("'" & B1& "'"&"!" & "A1")
=INDIRECT("'" & B1& "'"&"!" & C1)

please check out
http://www.mrexcel.com/board2/viewtopic.php?t=134225&start=0&postdays=0&postorder=asc&highlight=

Some notes on your performance problems...

1] Switching to SumProduct won't yield the performance boost you are looking for...

One possible route to spend cell space in order to get better performance. This can be achieved by creating additional ranges which concatenate the often-tested ranges. This move lead to SumProduct formulas with fewer terms or to faster SumIf formulas.

2] Avoid using volatile functions in particular INDIRECT and OFFSET if possible.

Regarding your INDIRECT question: If you have a few sheets say 5, you can switch to CHOOSE... Example:
Book7
ABCDEFGH
1Sheet List
2Sheet3a325Sheet1
3Sheet1b13Sheet2
4Sheet2a213Sheet3
5
6
Summary


C2, copied down:

=MATCH(A2,$H$2:$H$4,0)

D2, copied down:

=SUMIF(CHOOSE(C2,Sheet1!$A$2:$A10,Sheet2!$A$2:$A$20,Sheet3!$A$2:$A$40),B2,CHOOSE(C2,Sheet1!$B$2:$B10,Sheet2!$B$2:$B$20,Sheet3!$B$2:$B$40))

3] If you do a lot of lookups with match-type = 0, try to switch to one with the match-type set to 1 or a formula with LOOKUP...
 

ajones

Board Regular
Joined
Oct 26, 2002
Messages
104
Thanks for all the replies, I guess based on Aladin's previous posts I had always assumed I could convert most any array to a sumproduct to increase efficiency (speed, optimization).

I guess this is a case that is not true.

Based on the comments I assume my original formula of
{=SUM(IF( ( (LEFT('Working Sheet'!$A$1:$A$20000,2)= "51") + (LEFT('Working Sheet'!$A$1:$A$20000,2)= "52") + (LEFT('Working Sheet'!$A$1:$A$20000,2)= "53") ) * NOT(ISERROR('Working Sheet'!N$1:N$20000) ),('Working Sheet'!N$1:N$20000),"-")) }

is more efficient then then the suggestion of
=SUM(IF((LEFT('Working sheet'!$A$1:$A$20000,2)={"51","52","53"})*(ISNUMBER('Working sheet'!N$1:N$20000)),'Working sheet'!N$1:N$20000))


but how does my version compare with Domenic's
{=SUM(IF(ISNUMBER(MATCH(LEFT('Working Sheet'!$A$1:$A$20000,2),{"51","52","53"},0)),IF(ISNUMBER('Working Sheet'!N$1:N$20000),'Working Sheet'!N$1:N$20000))) }

in terms of speed/efficiency?

I like having all my conditions in one spot.... makes things cleaner, but speed is important.

I am sure I could increase speed by getting rid of the LEFT part and doing that work in the original table, but there is good chance that later I may need the left to vary from 1 character to 3 characters.

In doing some testing in a smaller test sheets I had cases where i could use a wild card * like "51*", "52*", "53*" and it wold work, but in other cases I could not get it to work when I move it over to larger examples. I thought about the wild card option at first as I thought it might be better then doing the LEFT().

What are the rules around when a wild card * like ( (a1:a20)="51*") work or does not work?

thanks again for all the help and information.

Alan



P.S. I am trying to put as much detail and keywords in the post in case others are doing searches on related issues. I have long found the advice in this forum wonderful either in questions I have asked or others.
 

ajones

Board Regular
Joined
Oct 26, 2002
Messages
104
Aladin,

You posted just before I posted my post. I have not had a chance to read it yet and saw it after I had already posted.

I will try and digest it and follow-up if needed.

You or others may have some additional thoughts on my follow-up post.

Alan
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Thanks for all the replies, I guess based on Aladin's previous posts I had always assumed I could convert most any array to a sumproduct to increase efficiency (speed, optimization).

I guess this is a case that is not true.

Judiciously set up, SumProduct formulas are faster. However, with very large ranges to test along with many terms, they won't deliver enough speed to achieve better perfomance you need.

Based on the comments I assume my original formula of
{=SUM(IF( ( (LEFT('Working Sheet'!$A$1:$A$20000,2)= "51") + (LEFT('Working Sheet'!$A$1:$A$20000,2)= "52") + (LEFT('Working Sheet'!$A$1:$A$20000,2)= "53") ) * NOT(ISERROR('Working Sheet'!N$1:N$20000) ),('Working Sheet'!N$1:N$20000),"-")) }

is more efficient then then the suggestion of
=SUM(IF((LEFT('Working sheet'!$A$1:$A$20000,2)={"51","52","53"})*(ISNUMBER('Working sheet'!N$1:N$20000)),'Working sheet'!N$1:N$20000))


but how does my version compare with Domenic's
{=SUM(IF(ISNUMBER(MATCH(LEFT('Working Sheet'!$A$1:$A$20000,2),{"51","52","53"},0)),IF(ISNUMBER('Working Sheet'!N$1:N$20000),'Working Sheet'!N$1:N$20000))) }

in terms of speed/efficiency?

The version Domenic suggested is better than yours.

I like having all my conditions in one spot.... makes things cleaner, but speed is important.

I am sure I could increase speed by getting rid of the LEFT part and doing that work in the original table, but there is good chance that later I may need the left to vary from 1 character to 3 characters.

In doing some testing in a smaller test sheets I had cases where i could use a wild card * like "51*", "52*", "53*" and it wold work, but in other cases I could not get it to work when I move it over to larger examples. I thought about the wild card option at first as I thought it might be better then doing the LEFT().

What are the rules around when a wild card * like ( (a1:a20)="51*") work or does not work?

Wild cards are not admissible (by design) in expresssions like the one you have in mind.

P.S. I am trying to put as much detail and keywords in the post in case others are doing searches on related issues. I have long found the advice in this forum wonderful either in questions I have asked or others.

Yes. I wish every help seeker could do that...
 

Forum statistics

Threads
1,137,202
Messages
5,680,145
Members
419,886
Latest member
Pulpboi

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