IF within SUMPRODUCT
- Thread starter Naceur
- Start date
NARAYANK991
Excel Ninja
Hi ,
SUMPRODUCT as an array formula does not need to be entered using CTRL SHIFT ENTER only if it does not contain any other function which requires to be entered using CTRL SHIFT ENTER to work correctly on arrays.
In your case , the IF function requires the usage of CTRL SHIFT ENTER to operate correctly on arrays.
By embedding this function within the SUMPRODUCT function , the formula itself requires to be entered using CTRL SHIFT ENTER , failing which it will not work correctly on arrays of data , and will not perform as an array formula.
Narayan
SUMPRODUCT as an array formula does not need to be entered using CTRL SHIFT ENTER only if it does not contain any other function which requires to be entered using CTRL SHIFT ENTER to work correctly on arrays.
In your case , the IF function requires the usage of CTRL SHIFT ENTER to operate correctly on arrays.
By embedding this function within the SUMPRODUCT function , the formula itself requires to be entered using CTRL SHIFT ENTER , failing which it will not work correctly on arrays of data , and will not perform as an array formula.
Narayan
Naceur
New Member
Hi,
@ Ramesh: Thank you very much for your solution. I actually wanted to avoid array formulas. I have a database of accounting entries of 250k lines. Array formulas may make it a very slow file to calculate.
@Narayan: If the IF function does not output an array, then why the formula is working when cell C14 has the value "ALL" (see result in cell E14)?
Also, if you select cell E12 and click on the "fx" sign on the formula bar, you can see that the result of the IF function is {1;1;0} ... doesn't that mean that the IF function IS generating an array?
Also if you select the whole IF function (in edit mode) and press F9, you can see that the result of the if function is an array.
I still think the behavior is weird and not consistent ... what do you think?
Regards,
Naceur
@ Ramesh: Thank you very much for your solution. I actually wanted to avoid array formulas. I have a database of accounting entries of 250k lines. Array formulas may make it a very slow file to calculate.
@Narayan: If the IF function does not output an array, then why the formula is working when cell C14 has the value "ALL" (see result in cell E14)?
Also, if you select cell E12 and click on the "fx" sign on the formula bar, you can see that the result of the IF function is {1;1;0} ... doesn't that mean that the IF function IS generating an array?
Also if you select the whole IF function (in edit mode) and press F9, you can see that the result of the if function is an array.
I still think the behavior is weird and not consistent ... what do you think?
Regards,
Naceur
NARAYANK991
Excel Ninja
Hi ,
Your IF statement is :
IF($C12="Any", {1;1;1}, ($C$4:$C$6=$C12)*1)
When C12 is put to Any , the result of the IF statement is a literal array , which means you have specified the array.
When C12 is put to any other value , the result of the IF statement is the array formed by the expression ($C$4:$C$6=$C12) , and this will result in an array only when the formula is entered using CTRL SHIFT ENTER.
To verify this , in any unused cells , enter the following two formulae , using only the ENTER key :
=SUM(IF($C12="Any", {1;1;1}, ($C$4:$C$6=$C12)*1))
=SUM(IF($C14="Any", {1;1;1}, ($C$4:$C$6=$C14)*1))
The result of the first formula should be 2 , while the result of the second formula should be 3.
Check out the results for yourself.
Re-enter the first formula as an array formula , using CTRL SHIFT ENTER , and see the result.
Narayan
Your IF statement is :
IF($C12="Any", {1;1;1}, ($C$4:$C$6=$C12)*1)
When C12 is put to Any , the result of the IF statement is a literal array , which means you have specified the array.
When C12 is put to any other value , the result of the IF statement is the array formed by the expression ($C$4:$C$6=$C12) , and this will result in an array only when the formula is entered using CTRL SHIFT ENTER.
To verify this , in any unused cells , enter the following two formulae , using only the ENTER key :
=SUM(IF($C12="Any", {1;1;1}, ($C$4:$C$6=$C12)*1))
=SUM(IF($C14="Any", {1;1;1}, ($C$4:$C$6=$C14)*1))
The result of the first formula should be 2 , while the result of the second formula should be 3.
Check out the results for yourself.
Re-enter the first formula as an array formula , using CTRL SHIFT ENTER , and see the result.
Narayan
Maybe , use "+" instead of IF within the SUMPRODUCT function
In F12,
=SUMPRODUCT(--($B$4:$B$6=$B12),($C12="any")+($C$4:$C$6=$C12),--($D$4:$D$6=$D12),$E$4:$E$6)
Edit :
1] In $C12 ="OAAA"
=($C12="any")+($C$4:$C$6=$C12)
=0+{1;1;0}
={1;1;0}
2] In $C14 ="any"
=($C14="any")+($C$4:$C$6=$C14)
=1+{0;0;0}
={1;1;1}
Regards
Bosco
In F12,
=SUMPRODUCT(--($B$4:$B$6=$B12),($C12="any")+($C$4:$C$6=$C12),--($D$4:$D$6=$D12),$E$4:$E$6)
Edit :
1] In $C12 ="OAAA"
=($C12="any")+($C$4:$C$6=$C12)
=0+{1;1;0}
={1;1;0}
2] In $C14 ="any"
=($C14="any")+($C$4:$C$6=$C14)
=1+{0;0;0}
={1;1;1}
Regards
Bosco
Last edited:
Naceur
New Member
Hi,
I still don't understand why the IF function generates an array when I click on the fx symbol on the formula bar. It even gives the correct final result (-116) [see image below].... maybe Excel is assuming it's an array formula before I even hit the Ctrl+Enter
... but if that's the way it works then that's the way it works ... and I should bear that in mind for future formulas.
Thanks again Narayan
Regards,
Naceur
I still don't understand why the IF function generates an array when I click on the fx symbol on the formula bar. It even gives the correct final result (-116) [see image below].... maybe Excel is assuming it's an array formula before I even hit the Ctrl+Enter
... but if that's the way it works then that's the way it works ... and I should bear that in mind for future formulas.
Thanks again Narayan
Regards,
Naceur
Naceur
New Member
You know what, I just tried with other simple formulas.
Ex: Sum ( (.....) * (....) * (....)) [with and without Ctrl+Enter]
Excel always gives the correct result if you open the dialog box by hitting the fx on the formula bar ... but if you don't use Ctrl+Enter then it shows an error result.
I guess I'm getting confused, that's all.
Thanks for your input
Ragards,
Naceur
Ex: Sum ( (.....) * (....) * (....)) [with and without Ctrl+Enter]
Excel always gives the correct result if you open the dialog box by hitting the fx on the formula bar ... but if you don't use Ctrl+Enter then it shows an error result.
I guess I'm getting confused, that's all.
Thanks for your input
Ragards,
Naceur
David Evans
Active Member
As @NARAYANK991 pointed out, you are using an Array formula when you use SUMPRODUCT - it's just that you don't have to enter it using the CTRL SHIFT ENTER. So if you think you're avoiding Array formulas ....
@Naceur: Your example does raise some interesting points. Here is my 2 cents:
Ctrl+Shift+Enter. As others have said fixes the issues, but may be a bit slower to recalculate. In my experience even adding a few extra functions to avoid CSE can be faster and is also more efficient when copying down rows.
Function wizard: The fx tool evaluates functions in isolation not in the context of the formula as entered on the sheet. The result shown in the formula is also what you get from entering in VBA immediate window:
Evaluate Formula: This tool is the only reliable way to debug a formula. Stepping through shows that without CSE $C$4:$C$6 is evaluated by "implicit intersection", i.e. by returning the intersection of the range from the same row or column. This returns #VALUE! as there is no intersection with row 12 so the overall formula returns an error. This part of the formula could be replaced by INDEX(($C$4:$C$6=$C12)*1,) to avoid CSE
IF function. This is a tricky function to understand in some ways as it is one of only a few functions that allows for any type of data (value, array or reference). The function wizard is showing that IF can return an array with an array input. The default return value of IF is a value, however, which means that CSE is usually needed to convert it to an array.
As a simple example consider the two formulas below:
=SUM(IF(1,{1;1;1}))
=SUM(ABS({1;1;1}))
While both return 3, clicking the fx tool indicates that the IF function is applied once to the whole array ({1;1;1}), whereas ABS and other similar functions that take value only arguments are applied multiple times - by iterating over each value (1).
Ctrl+Shift+Enter. As others have said fixes the issues, but may be a bit slower to recalculate. In my experience even adding a few extra functions to avoid CSE can be faster and is also more efficient when copying down rows.
Function wizard: The fx tool evaluates functions in isolation not in the context of the formula as entered on the sheet. The result shown in the formula is also what you get from entering in VBA immediate window:
Code:
?evaluate(activecell.Formula)Evaluate Formula: This tool is the only reliable way to debug a formula. Stepping through shows that without CSE $C$4:$C$6 is evaluated by "implicit intersection", i.e. by returning the intersection of the range from the same row or column. This returns #VALUE! as there is no intersection with row 12 so the overall formula returns an error. This part of the formula could be replaced by INDEX(($C$4:$C$6=$C12)*1,) to avoid CSE
IF function. This is a tricky function to understand in some ways as it is one of only a few functions that allows for any type of data (value, array or reference). The function wizard is showing that IF can return an array with an array input. The default return value of IF is a value, however, which means that CSE is usually needed to convert it to an array.
As a simple example consider the two formulas below:
=SUM(IF(1,{1;1;1}))
=SUM(ABS({1;1;1}))
While both return 3, clicking the fx tool indicates that the IF function is applied once to the whole array ({1;1;1}), whereas ABS and other similar functions that take value only arguments are applied multiple times - by iterating over each value (1).
i thought of a better example to illustrate the "trickiness" of the IF function...
Suppose you want to find attendance for a course from first day to last in columns A and B (where days attended are marked with an "x" and blank otherwise):
01-Sep
04-Sep x
05-Sep x
06-Sep
07-Sep x
08-Sep
11-Sep x
12-Sep
13-Sep
One option is to enter:
=SUM(INDEX(A:A,MATCH("~x",B:B,IF(1,{0,-1})))*{-1,1},1)
This returns 8 days (04-Sep to 11-Sep inclusive). However replacing IF(1,) with SIGN() or N() in the formula results in 1.
So what is the purpose of the IF(1,)?
(Basically this is due to IF(1,) returning an array/reference combination, and not just an array.)
Suppose you want to find attendance for a course from first day to last in columns A and B (where days attended are marked with an "x" and blank otherwise):
01-Sep
04-Sep x
05-Sep x
06-Sep
07-Sep x
08-Sep
11-Sep x
12-Sep
13-Sep
One option is to enter:
=SUM(INDEX(A:A,MATCH("~x",B:B,IF(1,{0,-1})))*{-1,1},1)
This returns 8 days (04-Sep to 11-Sep inclusive). However replacing IF(1,) with SIGN() or N() in the formula results in 1.
So what is the purpose of the IF(1,)?
(Basically this is due to IF(1,) returning an array/reference combination, and not just an array.)
Naceur
New Member
@Lori : wow, thanks very much. This is a lot of new tricks for me.
I liked your explanation about the "implicit intersection". I think this could be helpful to understand other functions.
With regard to your formula to find attendance, I'd like to ask the following:
I liked your explanation about the "implicit intersection". I think this could be helpful to understand other functions.
With regard to your formula to find attendance, I'd like to ask the following:
- what's the role of the Tilde sign before the "x"? .. "x" is not a wild card, is it? .. I removed the tilde and the result becomes 1
- I don't understand how the MATCH Function returns an array? and how it deals with the IF result. As a matter of fact, if I change the IF block with the result {0,-1} - (Actually, it is always equal to {0,-1}) - then I get 1 as the final result of the whole formula ... why?
- Can the INDEX Function return an array?
NARAYANK991
Excel Ninja
Hi Lori ,
I am confused by the following :
Your statement :
I assume then that days which do not have an "x" marked against them are not to be counted as attendance ; is this correct ?
If yes , then should the result for the data you have given be 4 or 8 ?
If it is 8 , then essentially the "x"s marked against the dates Sept 5 and Sept 7 do not matter , since the result would be 8 even if these two "x"s were removed ; is this correct ?
Narayan
I am confused by the following :
Your statement :
I assume then that days which do not have an "x" marked against them are not to be counted as attendance ; is this correct ?
If yes , then should the result for the data you have given be 4 or 8 ?
If it is 8 , then essentially the "x"s marked against the dates Sept 5 and Sept 7 do not matter , since the result would be 8 even if these two "x"s were removed ; is this correct ?
Narayan
@NARAYANK991, Yeah that wasn't phrased very clearly. What i meant was the time span from first day to last day. Another interpretation could be length of a work contract from first day to last worked and blanks are vacation or sick days.
@Naceur, you could also use "*" in place of "~x" (but then it might change the result if a header row were present). The formula result has to do with the combinations of types of data involved - i will try and provide a more detailed explanation when time permits.
@Naceur, you could also use "*" in place of "~x" (but then it might change the result if a header row were present). The formula result has to do with the combinations of types of data involved - i will try and provide a more detailed explanation when time permits.
Here are a few further notes relating to the questions raised above...
First off, contrary to what I said previously, tests on various formulas in the attachment indicate there is no significant difference with using CSE on formulas and that multi-cell array formulas can be fastest for returning a set of values from a list (tested with Office 2016 64 Bit.)
The INDEX/MATCH functions are each executed twice inside the formula, once with match_type = 0 and once with match_type = -1. These two cases treat the tilde differently.
match_type=0. The tilde is just an escape character for wildcards (*,? and ~) it has no effect on other characters. So in this case "~x" = "x" and the first match is returned.
match_type=-1. The tilde is matched according to the descending sort order. Since "~x" < "x", the last match is returned.
One way to find out the types of input / output of the MATCH and IF functions is:
1. Open the function wizard to show the basic input types: value (number, logical, text, any), reference or array...
=MATCH(any,number,number)
=IF(logical,any,any)
2. Enter a {1} for each input in the function wizard and check what is displayed next to the input box to see if array is an input/output type...
=MATCH({1},{1},{1}) -> MATCH(1,{1},1) =1
=IF({1},{1},{1}) -> IF({1},{1},{1}) ={1}
3. Enter A1 in each input and then step through with the evaluate tool to see if references get converted to values, if not then input/output is a reference type...
=SUM(MATCH(A1,A1,A1)) -> SUM(MATCH(1,A1,A1)) -> SUM(1)
=SUM(IF(A1,A1,A1)) -> SUM(IF(1,A1,A1)) -> SUM($A$1)
I find applying formatting for value, reference, array can help visualise things:
MATCH ( lookup_value, lookup_array, match_type )
IF ( logical_test, value_if_true, value_if_false )
Passing the IF result to the match_type argument forces an array return. It turns out this combination of Array+Reference output together with the Value+Reference input can be applied to return an array in general. (A complete list of functions with types was recently posted in the vault forum for reference.)
In effect, the array is being passed by reference in the match_type argument when IF(1,{0,-1}) is inserted as opposed to by value when just {0,-1} is used.
Yes, INDEX can return an array when the second or third argument is zero. The reference form of INDEX (in the notation above) is:
INDEX ( reference, row_num, column_num, area_num )
Because INDEX can return arrays it does not process arrays within value arguments. (According to OpenFormula documentation "Functions returning arrays are not eligible for implicit iteration").
So it seems any of the value inputs (row_num, column_num or area_num) need to be coerced to an array using a method similar to the MATCH+IF combination.
First off, contrary to what I said previously, tests on various formulas in the attachment indicate there is no significant difference with using CSE on formulas and that multi-cell array formulas can be fastest for returning a set of values from a list (tested with Office 2016 64 Bit.)
The INDEX/MATCH functions are each executed twice inside the formula, once with match_type = 0 and once with match_type = -1. These two cases treat the tilde differently.
match_type=0. The tilde is just an escape character for wildcards (*,? and ~) it has no effect on other characters. So in this case "~x" = "x" and the first match is returned.
match_type=-1. The tilde is matched according to the descending sort order. Since "~x" < "x", the last match is returned.
One way to find out the types of input / output of the MATCH and IF functions is:
1. Open the function wizard to show the basic input types: value (number, logical, text, any), reference or array...
=MATCH(any,number,number)
=IF(logical,any,any)
2. Enter a {1} for each input in the function wizard and check what is displayed next to the input box to see if array is an input/output type...
=MATCH({1},{1},{1}) -> MATCH(1,{1},1) =1
=IF({1},{1},{1}) -> IF({1},{1},{1}) ={1}
3. Enter A1 in each input and then step through with the evaluate tool to see if references get converted to values, if not then input/output is a reference type...
=SUM(MATCH(A1,A1,A1)) -> SUM(MATCH(1,A1,A1)) -> SUM(1)
=SUM(IF(A1,A1,A1)) -> SUM(IF(1,A1,A1)) -> SUM($A$1)
I find applying formatting for value, reference, array can help visualise things:
MATCH ( lookup_value, lookup_array, match_type )
IF ( logical_test, value_if_true, value_if_false )
Passing the IF result to the match_type argument forces an array return. It turns out this combination of Array+Reference output together with the Value+Reference input can be applied to return an array in general. (A complete list of functions with types was recently posted in the vault forum for reference.)
In effect, the array is being passed by reference in the match_type argument when IF(1,{0,-1}) is inserted as opposed to by value when just {0,-1} is used.
Yes, INDEX can return an array when the second or third argument is zero. The reference form of INDEX (in the notation above) is:
INDEX ( reference, row_num, column_num, area_num )
Because INDEX can return arrays it does not process arrays within value arguments. (According to OpenFormula documentation "Functions returning arrays are not eligible for implicit iteration").
So it seems any of the value inputs (row_num, column_num or area_num) need to be coerced to an array using a method similar to the MATCH+IF combination.
Last edited:
This is amazing Lori. Thanks for posting. I have always struggled to return parts of an array thru INDEX and the MATCH + IF combination looks interesting.