How to ignore criteria set in SUMIFS fuction if criteria cell is blank?

**Sam - excel rookie** · 08-28-2011, 04:25 PM

Hello excel experts,

I am creating a model where I need to add values from a database where multiple criteria are present for each row. I am using a sumifs function but the problem occurs when I leave a criteria cell blank than sumifs function is looking in the database for only blank cells in criteria range. What can I do in sumifs function to ignore criteria requirement if criteria is blank?

Thanks for all your help,

Sam

**teylyn** · 08-28-2011, 07:17 PM

Hello,

instead of leaving the criteria cell blank, enter a * sign. This will include all rows for that criterion.

cheers,

**Sam - excel rookie** · 08-28-2011, 10:27 PM

thank you sir your solution works.

**Sam - excel rookie** · 09-07-2011, 02:05 PM

Well I just tested the * symbal and the problem is that it does not include cells with numeric input. Any suggestions are welcome.

Thanks

**Sam - excel rookie** · 09-07-2011, 02:23 PM

also include blanks as well

**teylyn** · 09-07-2011, 11:14 PM

That means you want to do a sum of the whole range, without any condition, right?
So, do a simple sum if the condition cell is blank.

=if(A1="",Sum(B1:B100),Sumifs(B1:B100,C1:C100,A1))