SUMIFS Formula: Can it ignore a Blank Criteria field?

Hello Everyone,

I'm looking for a bit of creative thought on a SUMIFS forumla. Here's the problem:

I am creating a worksheet where I would like a user to be able to select one or more criteria and to then have the SUMIFS function caclulate the appropriate information, even if one of the applicable critera is left blank. As a simple example, in cell D1 I would have the formula below, with A1 as an input field for Expense Type, B1 as an input field for Location, and C1 as an input field for Organization.

So I would have the formula like this: =SUMIFS(Sum Range,Criteria Range 1,A1,Criteria Range 2,B1, Criteria Range 3,C1)

The goal would be to be able to put an input into each of the three inputs and get a result (For example: California is A1, Airfare is B1, and Human Resources is C1), or to be able to put an input in just two (or even one of the fields) and have the Sumifs formula ignore the empty field (For example California is A1, Airfare is B1). In a perfect world, my data would be pivot-table friendly and I could use slicers, but unfortunately it isnt (Dates as columns). Does anyone have a creative idea for this problem?

Thanks in advance,
Mike

Make the default subcriteria "*", which will still work against all values.

Richard's option 2 is genius.

Hello Everyone,

Thanks for all of the help and so quickly!- I tried using a wildcard character as my criteria, but the problem is that Excel wont sum rows that have a blank value. My data does have blank values in one of the criteria ranges, but not necessarily the other two. It looks like the wildcard will exclude the blank rows and not sum up everything I need it to. This is why I was hoping there was a creative way to get the formula to ignore the criteria field if there was no input value (in A1, B1 or C1). Is there a way around this?

Thanks,
Mike

Thanks for your help everyone!

I was able to find a seperate workaround that accomodated my worksheet.

Mike