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

Croydon_Poppy

New Member
Joined
Apr 23, 2013
Messages
2
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?

Thanks in advance.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
The formula should still work if the criteria stated is nil.

How do you want the formula to work if the criteria is blank?
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top