• 511K Members
• 6,103 Online
• 608K Conversations
SOLVED

New Contributor

# 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 THEFIRST COLUMN AND THE DIFFERENCE BETWEEN TWO COLUMN IS LESS THAN "5".

7 Replies
Solution

# Re: TO COUNT CELLS WITH MULTIPLE CRITERIA WITH COUNTIF FUNCTION.

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.

# Re: TO COUNT CELLS WITH MULTIPLE CRITERIA WITH COUNTIF FUNCTION.

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

# Re: TO COUNT CELLS WITH MULTIPLE CRITERIA WITH COUNTIF FUNCTION.

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.

# Re: TO COUNT CELLS WITH MULTIPLE CRITERIA WITH COUNTIF FUNCTION.

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

# Re: TO COUNT CELLS WITH MULTIPLE CRITERIA WITH COUNTIF FUNCTION.

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

# Re: TO COUNT CELLS WITH MULTIPLE CRITERIA WITH COUNTIF FUNCTION.

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

# Re: TO COUNT CELLS WITH MULTIPLE CRITERIA WITH COUNTIF FUNCTION.

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?

Related Conversations
How to count multiple values in a cell
Ugarte335 in Excel on
7 Replies
COUNTIF help
brentonhobden in Excel on
3 Replies
Counting Days
Tim Hunter in SQL Server on
2 Replies