Array Formulas

[Graham C1600]

Hi,

Is it possible to to use 2 x array formulas in the same function. I.e. {=sum(countifs(range,{"text","text"},range,{"text","text"}))}

I'm trying but it's only picking up the first text from the second array.

Any ideas ?

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Name[/td][td]Age[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]John[/td][td]

32​

[/td][td][/td][td]

2​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Jennifer[/td][td]

23​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Eric[/td][td]

41​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet36[/td][/tr][/table]

Array formula in cell D2:
=SUM(COUNTIFS(A2:A4,{"John","Eric"},B2:B4,{32,41}))

My formula seems to work?

 

[RoryA]

For all possible combinations of those, you need to alter the orientation of one of the arrays, like this:

=sum(countifs(range,{"text","text"},range,{"text";"text"}))

By the way, that formula does not need to be array entered, as they are array constants.

 

[RoryA]

My formula seems to work?

Try swapping the 32 and 41 around in the cells and you'll get 0 rather than the desired 2.

 

[Oscar Cronquist]

Try swapping the 32 and 41 around in the cells and you'll get 0 rather than the desired 2.

Sorry, I was as clear as mud. The COUNTIFS function counts records or rows if all criteria match. There is no matching record if I swap 32 and 41.

Example


[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Name[/td][td]Age[/td][td][/td][td]Criteria1[/td][td]Criteria2[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]John[/td][td]

32​

[/td][td][/td][td]John[/td][td]

41​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Jennifer[/td][td]

23​

[/td][td][/td][td]Eric[/td][td]

32​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Eric[/td][td]

41​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][td][/td][td]

0​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet36[/td][/tr][/table]

Array formula in cell D5:
=SUM(COUNTIFS(A2:A4,D2:D3,B2:B4,E2:E3))

 

[snjpverma]

This should work in such cases

=SUM(COUNTIFS(A2:A4,D2:D3,B2:B4,TRANSPOSE(E2:E3)))

 

[RoryA]

I believe the intent was to match on either value in each column. So per your example, to match if the name is John or Eric and if the age is 32 or 41; not to match John and 41, or Eric and 32.

 

[Graham C1600]

My original ask was to look up values in 1 column (perhaps 2 or 3 values) and then the same again in another column. So looking in multiple columns for multiple data.

So would

=sum(countifs(range,{"text","text"},range,{"text"​

;​

"text"}))​

work in my question by just changing the , for a ; in the second part of the formula ?

 

[RoryA]

It would only take a moment to find out...