Search code examples
excelexcel-formulaxlsxworksheet-functionworksheet

Counting Blank Cells in SUMIF Range

I have an excel sheet when I am trying to use SUMIF to add values based on the values of another range as below screenshot:

enter image description here

I am using the below SUMIF formula:

=SUMIF(B2:B560,"<=10000",A2:A560)

My target is to have the sum of cells in column A if their corresponding value in column B is less than or equal 10,000. My issue is that excel ignores the blank cells while I need them to be counted as less than 10,000. I can't manually replace blank cells because it is a long sheet.

I appreciate your time and support.

Solution

  • One option is an additional SUMIF:

    =SUMIF(B2:B560,"<=10000",A2:A560)+SUMIF(B2:B5260,"",A2:A560)

    enter image description here

    Another option is SUMPRODUCT:

    =SUMPRODUCT(A2:A560*(B2:B560<=10000))

    enter image description here

    A third option, if you have access to the FILTER function:

    =SUM(FILTER(A2:A560,B2:B560<=10000))

    enter image description here