Forum Discussion
SUMPRODUCT
Hi All,
Is there any way I can use sumproduct to count the number of times the difference between Column B and Column C is less than or equal to 1?
B-C <= 1
E.g. =SUMPRODUCT(--( B2:B10 - C2:C10 <=1 ) ) [DOESNT WORK]
5 Replies
If you don't have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter.
If you want to count the number of times the difference is less than or equal to one in either direction, use
=SUMPRODUCT(--(ABS(B2:B10-C2:C10)<=1))
again confirmed by pressing Ctrl+Shift+Enter if you have an older version of Excel.
- owensz-08Copper Contributor
Thank you so much for your help.
One problem I am facing is some rows of C2:C10 are empty.
Is there anyway i can write the formula such that it can still auto-calculate without making changes to the data?