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

#### K0st4din

##### Active Member
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!
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
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
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
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
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

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&"*"))``

#### K0st4din

##### Active Member
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
Glad we could help & thanks for the feedback.

Replies
1
Views
190
Replies
7
Views
34
Replies
2
Views
255
Replies
13
Views
311
Replies
22
Views
1K

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.

### 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