Forum Discussion

Anand_b_a's avatar
Anand_b_a
Copper Contributor
Jul 08, 2022

Hello there!

Hello there! I'm currently beginner to excel and i have some problem of writing formula. The problem is i can't seem to find solution of finding how much number there are within the range of -1 and 0 meaning from b1 to j1.I will really appreciate for someone helping this matter

 

P.S: I know adding minus, negativity to the 0 is not smart but this table is not exactly my work

excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jul 08, 2022

    Anand_b_a Try this:

    =COUNTIFS(B1:J1,"<=0",B1:J1,">=-1")

     

    If this doesn't work, let us know what Excel version you are on and if you get any error message.

     

    Another one would be:

    =SUMPRODUCT((B1:J1<=0)*(B1:J1>=-1))

    and should work in all Excel versions.

Resources