sumifs to add up by criteria if it there only?

[tonywatsonhelp]

Hi Everyone,

I want to do a sumifs formula but need some help,

the formula is "=SUMIFS('PL Raw'!I:I,'PL Raw'!E:E,Sheet5!A1,'PL Raw'!C:C,Sheet5!A3,'PL Raw'!A:A,Sheet5!A5)"

I have three dropdown boxes
Sheet5!A1
Sheet5!A3
Sheet5!A5
which are the 3 criteria,
but i have a problem,
if Sheet5!A3 = "" for example it takes this as my criteria, but i want it to sum everything unless there something in one of these cells,
so in other words if that critria is blank ignore it.

any ideas how i can do this?

thanks

Tony

 

[jasonb75]

It depends if the criteria are text or numbers, for text you could use this as the criteria in sumifs IF(Sheet5!A3="","*",Sheet5!A3)

For numbers, possibly IF(Sheet5!A3="","<>0",Sheet5!A3) although that would exclude any rows that actually contain 0 in the criteria range so may not be ideal.

 

[tonywatsonhelp]

Thanks Jason, but I've been playing about with your ideas cant seam to get anything
Anyone else got any ideas I can try,
you think it would be simple, if its easier, I could put the word "All" in instead of a blank
thanks
Tony

 

[jasonb75]

It may not be the most suitable but it was the best I could offer based on the information that you provided.

It depends if the criteria are text or numbers,

Simply clarifying points that are raised by people trying to help you can make a lot of difference in the answers provided.