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

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### 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
116
Replies
2
Views
134
Replies
13
Views
254
Replies
22
Views
1K
Replies
2
Views
58

1,128,089
Messages
5,628,609
Members
416,327
Latest member
Chimay

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