SUMIF criteria as array

[Jon von der Heyden]

Hi

Say I have the following formula:

=SUM(SUMIF(A2:A10,{"a","b"},B2:B10))

This will yield the result of all values in B2:B10 where the corresponding value in column A is either "a" or "b". No CSE required because of the inline array constant.

So lets say we place "a" and "b" in D1:D2 and use:

=SUM(SUMIF(A2:A10,D1:D2,B2:B10))

So say I do not commit with CSE. It will yield the result adjacent to "b", not "a". Why?

In other cases where an array is passed and not computed as an array Excel tends to yield the 1st array item. This picks up the last.

I realise this is academic but curious to know why??

 

[Matt Rogers]

Hi Jon.

a, b and a/b works correct.

Excel Workbook
	A	B	C	D	E	F	G	H
1		99			99			99
2	a	11		b	11		a	11
3	a	11		b	11		b	11
4	a	11		b	11		a	11
5	a	11		b	11		b	11
6	a	11		b	11		a	11
7	a	11		b	11		b	11
8	a	11		b	11		a	11
9	a	11		b	11		b	11
10	a	11		b	11		a	11
Sheet

 

[Sandeep Warrier]

It'll be interesting to know the answer to this!

 

[Colin Legg]

Jon,

Because you put the formula below (without CSE) in row 2.

=SUM(SUMIF(A2:A10,D1:D2,B2:B10))



If you put the formula in row 1 you would have got the sum corresponding to D1.

If you put the formula in row 3 you'll get 0 because there isn't a third item in the array.

So the position implies which value it calculates.

 

[Colin Legg]

I'm just going to reword my answer because it could be misconstrued.

Specifically, it is a "formula row" to "criteria range" intersection.

If your formula goes in row 3 then it will pick up the intersect of D1:D2 and 3:3 (which is nothing). If your formula goes in row 1 then it will pick up the intersect of D1:D2 and 1:1 (which is D1). etc...

 

[Jon von der Heyden]

You are so darn clever mate! How do you figure this stuff out?

Cheers

 

[rdgunn1]

Hi Jon.

a, b and a/b works correct.

Excel Workbook
	A	B	C	D	E	F	G	H
1		99			99			99
2	a	11		b	11		a	11
3	a	11		b	11		b	11
4	a	11		b	11		a	11
5	a	11		b	11		b	11
6	a	11		b	11		a	11
7	a	11		b	11		b	11
8	a	11		b	11		a	11
9	a	11		b	11		b	11
10	a	11		b	11		a	11
Sheet

What if I want my Array to be maintained & referenced in a cell instead of in the formula?