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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,195
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The formula should still work if the criteria stated is nil.

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

Croydon_Poppy

New Member
Joined
Apr 23, 2013
Messages
2
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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,199
Messages
5,835,936
Members
430,396
Latest member
dzifna

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
Top