Forum Discussion

MVP

Re: SUMPRODUCT

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.

4 Replies

owensz-08
Copper Contributor
Mar 22, 2023
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?
- HansVogelaar
  MVP
  Mar 22, 2023
  owensz-08
  
  How about
  
  =SUMPRODUCT((C2:C10<>"")*(B2:B10-C2:C10<1))
  - owensz-08
    Copper Contributor
    Mar 22, 2023
    HansVogelaar
    
    Hello, unfortunately it still gives an error "#VALUE!"
    Can anyone help to resolve this?