Show nothing if there is no data in one of the criteria

K0st4din

Active Member
Joined
Feb 8, 2012
Messages
385
Office Version
  1. 2013
  2. 2011
  3. 2010
  4. 2007
Platform
  1. Windows
Hello, everyone,
I ask for your help for the shown formula, which works and calculates everything correctly, but I can not handle this if in any of the cells in column C is empty (ie there is no data) the cell in which this formula is located show nothing (be empty). Because obviously I'm wrong and I don't know how to say, to do the same, but if there is no information it should be empty, and at the moment it gives me a total sum of a number (sum).
I get the result I get from several columns with data and dates!
Thank you in advance for your cooperation!
Rich (BB code):
=SUMIFS(Sheet1!$P$1:$P$300000;Sheet1!$D$1:$D$300000;">="&$F$2;Sheet1!$D$1:$D$300000;"<="&$G$2;Sheet1!$B$1:$B$300000;Sheet2!$A10;Sheet1!$J$1:$J$300000;"*"&Sheet2!$C10&"*")
 

Attachments

  • 2021-01-29_083510.jpg
    2021-01-29_083510.jpg
    151.3 KB · Views: 8

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
With one SUMIFS you create AND-relations. If one criterium is false then all are false = empty
Maybe you can solve this when you seperate your SUMIFS in multiple SUMIF.
 

K0st4din

Active Member
Joined
Feb 8, 2012
Messages
385
Office Version
  1. 2013
  2. 2011
  3. 2010
  4. 2007
Platform
  1. Windows
Ok, but then won't the formula become quite long. Could you write according to my formula, your idea. Thank you very much.
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Excel Formula:
=SUMIFS(Sheet1!$P$1:$P$300000;Sheet1!$D$1:$D$300000;">="&$F$2;Sheet1!$D$1:$D$300000;"<="&$G$2;Sheet1!$B$1:$B$300000;Sheet2!$A10;Sheet1!$J$1:$J$300000;"*"&Sheet2!$C10&"*")+SUMIFS(Sheet1!$P$1:$P$300000;Sheet1!$D$1:$D$300000;">="&$F$2;Sheet1!$D$1:$D$300000;"<="&$G$2;Sheet1!$B$1:$B$300000;Sheet2!$A10;Sheet1!$J$1:$J$300000;"")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,860
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Maybe
Excel Formula:
=IF(C10="","",SUMIFS(Sheet1!$P$1:$P$300000,Sheet1!$D$1:$D$300000,">="&$F$2,Sheet1!$D$1:$D$300000,"<="&$G$2,Sheet1!$B$1:$B$300000,Sheet2!$A10,Sheet1!$J$1:$J$300000,"*"&Sheet2!$C10&"*"))
 
Solution

K0st4din

Active Member
Joined
Feb 8, 2012
Messages
385
Office Version
  1. 2013
  2. 2011
  3. 2010
  4. 2007
Platform
  1. Windows
Many thanks to both of you!
I hadn't thought of this IF in the beginning.
Maybe because my formula itself has become quite long!
But now everything is as it should be!
You are just wonderful!
I wish you good health!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,860
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,142
Messages
5,640,371
Members
417,139
Latest member
bdmprasenjit

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