Forum Discussion

MURALI1408's avatar
MURALI1408
Copper Contributor
Aug 30, 2019
Solved

TO COUNT CELLS WITH MULTIPLE CRITERIA WITH COUNTIF FUNCTION.

TO COUNT CELLS WITH TWO CRITERIA. THERE ARE TWO CELLS WITH NUMBERS AS A VALUES. A     B 80   85 78   82 77   83 89   84 90   92 I WOULD LIKE TO COUNT IF THE VALUE IS LESS THAN "80" IN THEFIRS...
excel
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Aug 30, 2019

    MURALI1408 

     

    You may try the SUMPRODUCT Function like this...

     

    Assuming your numbers are in the range A1:B10, then try this

     

    =SUMPRODUCT((A1:A10<>"")*(A1:A10<80)*(ABS(A1:A10-B1:B10)<5))

     

    Adjust the ranges in the formula as per your requirement but don't refer the whole column in the formula as it would take a long time to calculate it.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 30, 2019

MURALI1408 

As variant that could be

=SUMPRODUCT((A1:INDEX(A:A,COUNTA(A:A))<80)*(ABS(A1:INDEX(A:A,COUNTA(A:A))-B1:INDEX(B:B,COUNTA(A:A)))<5))

assuming your data starts from first row of the sheet. 

Same with static range

=SUMPRODUCT((A1:A5<80)*(ABS(A1:A5-B1:B5)<5))
Subodh_Tiwari_sktneer's avatar
Subodh_Tiwari_sktneer
Silver Contributor
Aug 30, 2019

SergeiBaklan 

I included the criteria (A1:A10<>"") to exclude the blank cells in column A, if any, in the range A1:A10 otherwise the formula would return the wrong output.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 30, 2019

    Subodh_Tiwari_sktneer 

    yes, I skipped that intentionally using dynamic range, otherwise it won't work.

    • MURALI1408's avatar
      MURALI1408
      Copper Contributor
      Aug 30, 2019

      Mr.Sergei,

      thanks....but I want the count of columns matching these two criteria[conditions].....
      with =COUNTIF(A1:A5,<80), I got the count correct
      but for 2nd condition,[difference of A1 & B1 is less than "5") couldn't make.....
      please help
    • MURALI1408's avatar
      MURALI1408
      Copper Contributor
      Aug 30, 2019

      Mr.Sergei,

      thanks....but I want the count of columns matching these two criteria[conditions].....
      with =COUNTIF(A1:A5,<80), I got the count correct
      but for 2nd condition,[difference of A1 & B1 is less than "5") couldn't make.....
      please help
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Aug 30, 2019

        MURALI1408 

        In your sample there is only one such combination and the formula gives correct result

        Could you please clarify what exactly doesn't work, which result do you expect?

Resources