Array to Sumproduct excluding errors multi conditions

[ajones]

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=

 

[pgc01]

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]

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!

 

[pgc01]

Hi again

Edit: Inefficient formula

HTH
PGC

 

[Aladin Akyurek]

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.

 

[pgc01]

You are right Aladin, thanks.
PGC

 

[Aladin Akyurek]

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
	A	B	C	D	E	F	G	H
1								Sheet List
2	Sheet3	a	3	25				Sheet1
3	Sheet1	b	1	3				Sheet2
4	Sheet2	a	2	13				Sheet3
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]

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]

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]

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