Thanks:  0
Likes:  0

# Thread: COUNTIFS with different array range

1. ## COUNTIFS with different array range

Is it possible to derive a countifs using two criteria with different array range - one horizontal and one vertical. Please see example below

 a b c d e f g h i j 1 Name John George Alex Allicia 2 s1 1 1 0 0 3 s2 0 0 0 0 4 s3 1 0 1 0 5 s4 0 0 0 1 6 Name s1 s2 s3 s4 s5 0 0 0 0 7 John 1 0 1 0 0 ?? 8 George 1 0 0 0 0 ?? ?? 9 Alex 0 0 1 0 0 ?? ?? ?? 10 Allicia 0 0 0 1 0 ?? ?? ?? ??

i would to see how many pair of "1" for each name in G7, G8, G9, G10, H8, H9, H10, I9, I10, J10
i try these =COUNTIFS(G2:G6;1;B7:F7;1) but no luck

thanks

2. ## Re: COUNTIFS with different array range

You could try just COUNTIF but use it twice
=COUNTIF(G2:G6,1) + COUNTIF(B7:F7,1)

3. ## Re: COUNTIFS with different array range

yatmo, welcome to the Forum!

You haven't given us your expected results, but I'm guessing you're trying to do this?

Names: =G1:J1
Results: =G2:J6
G9: =SUMPRODUCT(INDEX(Results,,MATCH(G\$8,Names,)),INDEX(Results,,MATCH(\$F9,Names,)))

FGHIJ
1NameJohnGeorgeAlexAllicia
2s11100
3s20000
4s31010
5s40001
6s50000
7
8JohnGeorgeAlexAllicia
9John2
10George11
11Alex101
12Allicia0001

4. ## Re: COUNTIFS with different array range

Originally Posted by jimrward
You could try just COUNTIF but use it twice
=COUNTIF(G2:G6,1) + COUNTIF(B7:F7,1)
Originally Posted by StephenCrump
yatmo, welcome to the Forum!

You haven't given us your expected results, but I'm guessing you're trying to do this?

Names: =G1:J1
Results: =G2:J6
G9: =SUMPRODUCT(INDEX(Results,,MATCH(G\$8,Names,)),INDEX(Results,,MATCH(\$F9,Names,)))

F G H I J
1 Name John George Alex Allicia
2 s1 1 1 0 0
3 s2 0 0 0 0
4 s3 1 0 1 0
5 s4 0 0 0 1
6 s5 0 0 0 0
7
8 John George Alex Allicia
9 John 2
10 George 1 1
11 Alex 1 0 1
12 Allicia 0 0 0 1

thanks alot for both answer, but its not just sum the number from the table, its a code or criteria
1-1 mean that both name present in each "s".

If i use that function, i got trouble with 1-0 or 0-1 criteria

1 mean Present and 0 mean absent

any suggestion ??

5. ## Re: COUNTIFS with different array range

Originally Posted by yatmo
any suggestion ??
We can only guess what formula you need, if you don't tell us the results you are expecting to see.

For the screenshot first posted, what results do you expect to see in G7:G10, H8:H10, I9:I10, and J10, and why?

6. ## Re: COUNTIFS with different array range

the F8:J12 is exactly result that i want to see for 1-1,

Originally Posted by StephenCrump

F G H I J
1 Name John George Alex Allicia
2 s1 1 1 0 0
3 s2 0 0 0 0
4 s3 1 0 1 0
5 s4 0 0 0 1
6 s5 0 0 0 0
7
8 John George Alex Allicia
9 John 2
10 George 1 1
11 Alex 1 0 1
12 Allicia 0 0 0 1

but how to count 1-0 or 0-1 or 0-0 for pair of names

take example, John and George, they have
1x "1-1" from s1,
3x "0-0" from s2,s4,s5, and
1x "1-0"
0x "0-1"

John and George, they have
1x "1-1" from s3
1x "1-0" from s1
3x "0-0" from s2,s3,s5
0x "0-1"

John and Allicia
2x "1-0"
1x "0-1"
2x "0-0"
0x "1-1"

7. ## Re: COUNTIFS with different array range

Maybe this (to be entered using Ctrl+Shift+Enter, not just Enter):

=SUMPRODUCT(--TRANSPOSE(G\$2:G\$6=1),--(\$B7:\$F7=1))

8. ## Re: COUNTIFS with different array range

Originally Posted by Tetra201
Maybe this (to be entered using Ctrl+Shift+Enter, not just Enter):

=SUMPRODUCT(--TRANSPOSE(G\$2:G\$6=1),--(\$B7:\$F7=1))
you are the best, Sir. Thanks !