sumifs with multiple criteria if not show blank in excel

[Lukma]

Hi Guys

EID Mubarak could anyone help out with the solution i have a formula with sumifs to sum Hours based on multiple criteria, after dragging formula down i need where nothing to show blank rather than showing zero in cell

I'd like the sum to return results where one part of the criteria is blank - to return blank cell rather having zero is this possible? Thank you.

[=SUMIFS($L$18:$L$20,$I$18:$I$20,Z$17,$D$18:$D$20,$V18,$C$18:$C$20,$X18,$B$18:$B$20,$B$9)]

 

[Fluff]

How about

=IFERROR(1/(1/SUMIFS($L$18:$L$20,$I$18:$I$20,Z$17,$D$18:$D$20,$V18,$C$18:$C$20,$X18,$B$18:$B$20,$B$9)),"")

 

[Lukma]

Hi Fluff
Great awesome, Previously i was using the option of Excel by clicking the Show no Zero in cell , but now am good to good without having all my Sheet ignore zero

Thanks

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Lukma]

Hi Fluff

Just a Quick One am also having this formula in Column X with ctrl Shift Enter and i really don't want to use the Array Formula is there other formula to extract the unique based on Criteria am having 2016 excel

In column B i have Month and in column i Have the Repeated Value for each name and in D i have the Name listed and in column V i created permanent max 11 entry for each names and in Column X i have my formula to extract the unique value based on The Criteria for every month.. so is there any other option without using Ctrl Shift Enter.

[=IFERROR(INDEX($C$18:$C$60000, MATCH(0, COUNTIF(X$17:$X17, $C$18:$C$60000)+IF($D$18:$D$60000<>V18, 1, 0)+IF($B$18:$B$60000<>$B$9, 1, 0), 0)), "")]

 

[Fluff]

As this is totally different from your op, it needs a new thread. Thanks

 

[Lukma]

Hi

Thanks appreciate

 

[Fluff]

You're welcome.