SUMIF that returns a Zero if only Zeros have been entered, but returns a blank if nothing entered

[Helluvaname]

Hi excel guru's, I hope you can help an excel novice!

I have a budget sheet with the following data:

Col B = An expense category ref (usually a number)
Col J = Amount Spent
Rows 53 : 118 contain the above data

I'd like to have a cell (say K5) that displays the sum of the values in Col J, where a specified "category ref" appears in Col B, and to do that I am using:
=SUMIF(B$53:B$118,B5,J$53:J$118) where B5 contains the desired "category ref"

However, I'd like K5 to display 0 if there are entries in the range J53:J118 (where there is the required "category ref" in B53:B118) that contain 0, which it does BUT, I'd like K5 to be blank if there are no entries in the range.

Hopefully that makes sense and someone can suggest a simple solution?! (did I mention I'm a novice!)

 

[etaf]

does this work for you

=IF( COUNTIF( B$53:B$118,B5)=0, "", SUMIF(B$53:B$118,B5,J$53:J$118))

So if the Catergory Ref , in B5 does not exist , rather than zero it would be blank, but if it does exist with a zero result it will still display zero 0

 

[jasonb75]

This should do it

=IF(COUNTIFS($B$53:B$118,B5,J$53:J$118,"<>"),SUMIFS(J$53:J$118,B$53:B$118,B5),"")

 

[Helluvaname]

does this work for you

=IF( COUNTIF( B$53:B$118,B5)=0, "", SUMIF(B$53:B$118,B5,J$53:J$118))

So if the Catergory Ref , in B5 does not exist , rather than zero it would be blank, but if it does exist with a zero result it will still display zero 0

Sorry probably my explanation, but it's if the data in J53:J118 is blank that I wanted the result in K5 to be blank.

 

[Helluvaname]

This should do it

=IF(COUNTIFS($B$53:B$118,B5,J$53:J$118,"<>"),SUMIFS(J$53:J$118,B$53:B$118,B5),"")

Ah, That it!!
Many thanks Jasonb75

 

[Helluvaname]

@jasonb75 sorry one more question:
I'd like a "balance column to also show a zero or blank (or sum result) depending on the result of the spend column.
I've attached the above example to show what I mean.
Is that easy to do?

 

[jasonb75]

That one is a bit easier

=IF(K5="","",J5-K5)

I'm guessing that you made an error in row 6? 100 - 0 = 0 ?

 

[Helluvaname]

? yes, I did! and thanks for the speedy response, very much appreciated, cheers ?