Aug 30 2019 03:19 AM
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".
1ST CRITERIA CAN BE ACHIEVED BY USING "<80". BUT I COULDN'T ACHIEVE 2ND CRITERIA. PLEASE HELP
Aug 30 2019 03:30 AM - edited Aug 30 2019 03:31 AM
Solution
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.
Aug 30 2019 03:32 AM
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))
Aug 30 2019 03:41 AM
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.
Aug 30 2019 04:05 AM
yes, I skipped that intentionally using dynamic range, otherwise it won't work.
Aug 30 2019 04:17 AM
Mr.Sergei,
Aug 30 2019 04:20 AM
Mr.Sergei,
Aug 30 2019 07:15 AM
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?
Aug 30 2019 03:30 AM - edited Aug 30 2019 03:31 AM
Solution
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.