# How to Sum a Range of Values when one of the Criteria is 'null'

#### Croydon_Poppy

##### New Member
Hi

I am using the following formula

=SUMPRODUCT((Index!\$A\$1:\$A\$5000=Main!C4)*(Index!\$B\$1:\$B\$5000=Main!C5)*(Index!\$C\$1:\$C\$5000=Main!C6)*(Index!\$D\$1:\$D\$5000=Main!C7)*(Index!\$E\$1:\$E\$5000=Main!C8)*(Index!\$F\$1:\$F\$5000=Main!C9)*Index!\$G\$1:\$G\$5000)

This works as long as one of the (C4, C5, C6, C7, C8, C9) cells has a value. But this formula stops working when any of the above cells becomes a null (is left blank). Can someone please help me with this?

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
The formula should still work if the criteria stated is nil.

How do you want the formula to work if the criteria is blank?

The formula should still work if the criteria stated is nil.

How do you want the formula to work if the criteria is blank?

Sorry If I was not clear in my post. I wanted the formula to work even if one/or more of the criteria is missing (read blank or null).
Anyway just achieved it with the DSUM formula with a twist. I am posting this for anyone who faces such an issue in future.

First Create a Separate Section where you determine which User Inputs are blank or non-blank

 Criteria1 =IF(UserInput1="","<>* or =*",UserInput1) Criteria2 =IF(UserInput2="","<>* or =*",UserInput2) Criteria3 =IF(UserInput3="","<>* or =*",UserInput3) Criteria4 =IF(UserInput4="","<>* or =*",UserInput4) Criteria5 =IF(UserInput5="","<>* or =*",UserInput5) Criteria6 =IF(UserInput6="","<>* or =*",UserInput6)

<tbody>
</tbody>

Name the above Range as say 'Determine'

Now use the DSUM formula to get the desired results. In the example below I am trying to Sum all 'Bad Apples' based on the Six criteria. So the formula goes as such:

= DSUM (Database, "Bad Apples", Determine)

Replies
8
Views
155
Replies
2
Views
142
Replies
2
Views
224
Replies
10
Views
275
Replies
9
Views
160

1,207,255
Messages
6,077,314
Members
446,278
Latest member
hoangquan2310

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back