Forum Discussion

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
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

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
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
  Diamond Contributor
  Aug 30, 2019
  Subodh_Tiwari_sktneer
  
  yes, I skipped that intentionally using dynamic range, otherwise it won't work.
  - 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