Forum Discussion

Andrew Whitfield's avatar
Andrew Whitfield
Copper Contributor
Jan 22, 2018
Solved

COUNTIF to count numbers between 1 and 9, down two colums.

Can use COUNTIFS to count down two columns (Arrays) to count numbers between say 1 & 9. But can not add more logic, to count down both columns were there are numbers between 1 & 9 in adjacent rows.

Using columns Ball 1 and Ball 2 there are only three positions with numbers between 1 and 9 in both columns, the first line, the fifth and seventh. So the COUNTIF result should be 3.  But using COUNTIFS it counts all numbers between 1 and 9 and comes back with eleven.  Can anyone help please. Have tried adding AND to criteria, but just get  format errors. 

  • Andrew,

     

    Try this:

     

    =COUNTIFS(BALL_1_RANGE,">=1",BALL_1_RANGE,"<=9",BALL_2_RANGE,">=1",BALL_2_RANGE,"<=9")

     

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor

    Andrew,

     

    Try this:

     

    =COUNTIFS(BALL_1_RANGE,">=1",BALL_1_RANGE,"<=9",BALL_2_RANGE,">=1",BALL_2_RANGE,"<=9")

     

Resources