COUNTIFS with different array range
Attend Excelapalooza
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: COUNTIFS with different array range

  1. #1
    New Member
    Join Date
    Mar 2018
    Location
    java
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular jimrward's Avatar
    Join Date
    Feb 2003
    Location
    Kingdom of Fife
    Posts
    1,624
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIFS with different array range

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

  3. #3
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,254
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default 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



    Last edited by StephenCrump; Mar 18th, 2018 at 07:06 PM.

  4. #4
    New Member
    Join Date
    Mar 2018
    Location
    java
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIFS with different array range

    Quote Originally Posted by jimrward View Post
    You could try just COUNTIF but use it twice
    =COUNTIF(G2:G6,1) + COUNTIF(B7:F7,1)
    Quote Originally Posted by StephenCrump View Post
    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. #5
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,254
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIFS with different array range

    Quote Originally Posted by yatmo View Post
    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. #6
    New Member
    Join Date
    Mar 2018
    Location
    java
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIFS with different array range

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


    Quote Originally Posted by StephenCrump View Post

    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. #7
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    3,000
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    New Member
    Join Date
    Mar 2018
    Location
    java
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIFS with different array range

      
    Quote Originally Posted by Tetra201 View Post
    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 !

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com