Forum Discussion

Copper Contributor

Mar 22, 2023

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

excel

HansVogelaar

MVP

Mar 22, 2023

owensz-08

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-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 23, 2023
  HansVogelaar
  
  Hello, unfortunately it still gives an error "#VALUE!"
  Can anyone help to resolve this?
  - HansVogelaar
    MVP
    Mar 23, 2023
    owensz-08
    
    Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?