COUNTIFS with different array range

Y

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

2
3
4
5

<tbody>
[TD="width: 192"]a[/TD]
[TD="width: 33"]b[/TD]
[TD="width: 33"]c[/TD]
[TD="width: 33"]d[/TD]
[TD="width: 33"]e[/TD]
[TD="class: xl68, width: 33"]f[/TD]
[TD="class: xl66, width: 33"]g[/TD]
[TD="class: xl65, width: 33"]h[/TD]
[TD="class: xl65, width: 33"]i[/TD]
[TD="class: xl65, width: 33"]j[/TD]

[TD="width: 192"]1[/TD]
[TD="width: 192"][/TD]
[TD="width: 33"][/TD]
[TD="width: 33"][/TD]
[TD="width: 33"][/TD]
[TD="width: 33"][/TD]
[TD="class: xl68, width: 33"]Name[/TD]
[TD="class: xl66, width: 33"]John[/TD]
[TD="class: xl65, width: 33"]George[/TD]
[TD="class: xl65, width: 33"]Alex[/TD]
[TD="class: xl65, width: 33"]Allicia[/TD]

[TD="class: xl70"]s1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]0[/TD]

[TD="class: xl70"]s2[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]0[/TD]

[TD="class: xl70"]s3[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]

[TD="class: xl70"]s4[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]

[TD="class: xl69"]6[/TD]
[TD="class: xl69"]Name[/TD]
[TD="class: xl70"]s1[/TD]
[TD="class: xl70"]s2[/TD]
[TD="class: xl70"]s3[/TD]
[TD="class: xl70"]s4[/TD]
[TD="class: xl70"]s5[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]0[/TD]

[TD="class: xl66"]7[/TD]
[TD="class: xl66"]John[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"] ??[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl65"]8[/TD]
[TD="class: xl65"]George[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"] ??[/TD]
[TD="class: xl67"]?? [/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]

[TD="class: xl65"]9[/TD]
[TD="class: xl65"]Alex[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"] ??[/TD]
[TD="class: xl67"]?? [/TD]
[TD="class: xl67"]?? [/TD]
[TD="class: xl67"][/TD]

[TD="class: xl65"]10[/TD]
[TD="class: xl65"]Allicia[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"]1[/TD]
[TD="class: xl67"]0[/TD]
[TD="class: xl67"] ??[/TD]
[TD="class: xl67"]?? [/TD]
[TD="class: xl67"]?? [/TD]
[TD="class: xl67"]?? [/TD]

</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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
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
jimrward said:
You could try just COUNTIF but use it twice
=COUNTIF(G2:G6,1) + COUNTIF(B7:F7,1)
Click to expand...

StephenCrump said:
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]George[/TD]
[TD="align: center"]Alex[/TD]
[TD="align: center"]Allicia[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]s1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]s2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]s3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]s4[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]s5[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]George[/TD]
[TD="align: center"]Alex[/TD]
[TD="align: center"]Allicia[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]George[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]Alex[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]Allicia[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]

</tbody>
Click to expand...

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
yatmo said:
any suggestion ??
Click to expand...

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,


StephenCrump said:
FGHIJ

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]George[/TD]
[TD="align: center"]Alex[/TD]
[TD="align: center"]Allicia[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]s1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]s2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]s3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]s4[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]s5[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]George[/TD]
[TD="align: center"]Alex[/TD]
[TD="align: center"]Allicia[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]George[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]Alex[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]Allicia[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]

</tbody>
Click to expand...

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
You must log in or register to reply here.

Similar threads

GMC The Macro Man
  • Solved
SUM COUNTIFS with AND + OR
Replies
3
Views
143
Fluff
Fluff
A
  • Question Question
Attempting to auto generate ID names based on uncertain parameters
Replies
1
Views
253
Rijnsent
Rijnsent
P
  • Question Question
Count names based on enrollment
Replies
2
Views
229
paulkc
P
F
  • Question Question
Scoresheet creation for a trivia game
Replies
1
Views
337
Rijnsent
Rijnsent
J
  • Solved
find oldest value
Replies
9
Views
287
JOEE1979
J

Forum statistics

Threads
1,223,896
Messages
6,175,263
Members
452,627
Latest member
KitkatToby

Share this page

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