Sumifs return 0 - I want it to return blank. (unless the correct value is 0)

[Martinpetersson]

If excel wont find a match it will return "0" I don't want that.

Reason why I use sumifs is because I need to return a value with two criterias.
Are there any other formulas that I can use for returning a value based on two criterias?

I don't need to sum nothing because it's always only one cell value to return. But I Use sumifs because of the v-lookup didn't work with multiple criterias.

Thank you in advance.

Samling.xlsx
	A	B	C	D	E	F	G
3	2022-06-08	1		2	0	0	2
All

Cell Formulas
Range	Formula
D3	D3	=SUMIFS('S-FORM'!$I:$I,'S-FORM'!$F:$F,"gul",'S-FORM'!$J:$J,A3)
E3	E3	=SUMIFS('S-FORM'!$I:$I,'S-FORM'!$F:$F,"blå",'S-FORM'!$J:$J,A3)
F3	F3	=SUMIFS('S-FORM'!$I:$I,'S-FORM'!$F:$F,"grön",'S-FORM'!$J:$J,A3)
G3	G3	=D3+E3+F3

 

[Fluff]

If excel wont find a match it will return "0" I don't want that.

Then what do you want?
If you want it to return "" then the formula in G3 will fail

 

[Joe4]

Why not just apply a custom format to those cells to hide the zeroes?
See: Display or hide zero values

 

[Martinpetersson]

You’re right! Haven’t thought about that part yet. But G3 is not really an important value for me.

However, the values that I want to return comes from an office form and those values are ratings from 0-10 but if someone forgets to send in their ratings for that date, my document will show “0” and then it will look like they sent in “0”as an answer.

But remember, sometime 0 will be the correct value.

 

[Martinpetersson]

Why not just apply a custom format to those cells to hide the zeroes?
See: Display or hide zero values

Unfortunately sometimes 0 will be correct.

 

[Fluff]

Ok, how about

=FILTER('S-FORM'!$I:$I,('S-FORM'!$F:$F="gul")*('S-FORM'!$J:$J=A3),"")

 

[Joe4]

If you could return "" instead of 0, then you can just change this formula:

=D3+E3+F3

to this:

=SUM(D3:F3)

as SUM will not choked on the "" values like + does.

EDIT:
And looks like Fluff just posted a formula that might just do that for you.

 

[Martinpetersson]

Thanks for trying to help me.

Formula I use now:

D3 =FILTER('S-FORM'!$I:$I;('S-FORM'!$F:$F="Gul")*('S-FORM'!$J:$J=A3);"No results")
E3 =FILTER('S-FORM'!$I:$I;('S-FORM'!$F:$F="BLÅ")*('S-FORM'!$J:$J=A3);"No results")
F3 =FILTER('S-FORM'!$I:$I;('S-FORM'!$F:$F="GRÖN")*('S-FORM'!$J:$J=A3);"No results")


This is what 'S-form' sheet look like.

Value "2" should be returned in D3. (Above)
"Blå" or "Grön" doesn't appear on that date (2022-06-08) so
"No results" should be returned in E3 and F3 (Above)

Suggestions please. Thank you so much!

 

[RoryA]

It looks like column J is text, not a date, in which case you could use:

=FILTER('S-FORM'!$I:$I;('S-FORM'!$F:$F="Gul")*('S-FORM'!$J:$J=TEXT(A3;"yyyy-mm-dd"));"No results")

 

[Martinpetersson]

Yes, I can use text in that column but it dosent seem to be working anyway. Still returning"no results"