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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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
191
Fluff
Fluff
A
  • Question Question
Attempting to auto generate ID names based on uncertain parameters
Replies
1
Views
254
Rijnsent
Rijnsent
T
  • Question Question
Find highest scorers and return their name
2
Replies
13
Views
252
Peter_SSs
Peter_SSs
P
  • Question Question
Count names based on enrollment
Replies
2
Views
235
paulkc
P
E
  • Question Question
extracting data with sorted and subtotal
Replies
1
Views
92
earth
E

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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