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.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,563
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,474
Members
414,070
Latest member
DuncanLucas

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