Counting Blank Cells in SUMIF Range

0
votes

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.

1
excelexcel-formulaxlsxworksheet-functionworksheet

1 Answers

2
votes

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