COUNTIFS with different array range

yatmo

New Member
Joined
Mar 18, 2018
Messages
4
Is it possible to derive a countifs using two criteria with different array range - one horizontal and one vertical. Please see example below

abcdefghij
1NameJohnGeorgeAlexAllicia
2s11100
3s20000
4s31010
5s40001
6Names1s2s3s4s50000
7John10100 ??
8George10000 ????
9Alex00100 ???? ??
10Allicia00010 ???? ?? ??

<tbody>
</tbody>





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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
You could try just COUNTIF but use it twice
=COUNTIF(G2:G6,1) + COUNTIF(B7:F7,1)
 
Upvote 0
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,)))


Book1
FGHIJ
1NameJohnGeorgeAlexAllicia
2s11100
3s20000
4s31010
5s40001
6s50000
7
8JohnGeorgeAlexAllicia
9John2
10George11
11Alex101
12Allicia0001
Sheet1
 
Last edited:
Upvote 0
You could try just COUNTIF but use it twice
=COUNTIF(G2:G6,1) + COUNTIF(B7:F7,1)

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

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 ??
 
Upvote 0
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?
 
Upvote 0
the F8:J12 is exactly result that i want to see for 1-1,


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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

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"
 
Upvote 0
Maybe this (to be entered using Ctrl+Shift+Enter, not just Enter):

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

Forum statistics

Threads
1,212,933
Messages
6,110,759
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top