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]

Check that the values in col F & in D2 do not have any leading/trailing spaces.

 

[RoryA]

Also, for your regional settings, you might need to alter the format codes to whatever you normally use for year, month and day.

 

[Martinpetersson]

Thanks again for trying to help. Nothing is seem to be working.
I'm trying to give you the whole document divided in two mini sheets (one for each sheet)

Here is the main sheet

Test_excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	S-form
2	Date	Yellow	Blue	Green	Total		Date	Yellow	Blue	Green	Total
3	2022-06-07	No results	No results	No results	0		2022-06-07	5	No results	No results	5
4	2022-06-08	No results	No results	No results	0		2022-06-08	2	8	No results	10
5	2022-06-09	No results	No results	No results	0		2022-06-09	3	1	0	4
6
7	This is what I have now						This is what I want to have
All

Cell Formulas
Range	Formula
B3:B5	B3	=FILTER('S-FORM'!$I:$I,('S-FORM'!$F:$F="yellow")*('S-FORM'!$J:$J=TEXT(A3,"yyyy-mm-dd")),"No results")
C3:C5	C3	=FILTER('S-FORM'!$I:$I,('S-FORM'!$F:$F="Blue")*('S-FORM'!$J:$J=TEXT(A3,"yyyy-mm-dd")),"No results")
D3:D5	D3	=FILTER('S-FORM'!$I:$I,('S-FORM'!$F:$F="green")*('S-FORM'!$J:$J=TEXT(A3,"yyyy-mm-dd")),"No results")
E3:E5,K3:K5	E3	=SUM(B3:D3)

Here is the 'S-form' sheet. Originally this sheet contains a table that collects new data up to three times each day.

Test_excel.xlsx
	A	B	C	D	E	F	G	H	I	J
1	ID	Start time	Completion time	Email	Name	Vilken skiftfärg hade föregående skift?	Användes 5S tavlan på föregående skift?	Hur många avvikelser kan du se?	Hur många avvikelser kan du se?2	Shortdate
2	8	2022-06-07 14:28	2022-06-07	anonymous		Yellow			5	2022-06-07
3	9	2022-06-08 10:26	2022-06-08	anonymous		Yellow			2	2022-06-08
4	10	2022-06-08 14:28	2022-06-08	anonymous		Blue			8	2022-06-08
5	11	2022-06-09 10:26	2022-06-09	anonymous		Green			0	2022-06-09
6	12	2022-06-09 14:28	2022-06-09	anonymous		Yellow			3	2022-06-09
7	13	2022-06-09 10:26	2022-06-09	anonymous		Blue			1	2022-06-09
S-FORM

 

[RoryA]

What do:
=ISNUMBER(A3)
=ISNUMBER('S-FORM'!$J$2)
=TEXT(44722,"yyyy-mm-dd")

return if you enter them on the summary sheet?

 

[Martinpetersson]

What do:
=ISNUMBER(A3)
=ISNUMBER('S-FORM'!$J$2)
=TEXT(44722,"yyyy-mm-dd")

return if you enter them on the summary sheet?

Where am I going to put those formulas?

If I put them in the summary sheet
j2 will return as false.

Samling.xlsx
	A	B	C	D	E	F	G	H	I	J
1	ID	Start time	Completion time	Email	Name	Vilken skiftfärg hade föregående skift?	Användes 5S tavlan på föregående skift?	Hur många avvikelser kan du se?	Hur många avvikelser kan du se?2	Shortdate
2	8	2022-06-07 14:28	2022-06-07	anonymous		Grön			3	2022-06-07
3	9	2022-06-08 10:26	2022-06-08	anonymous		Gul			2	2022-06-08
S-FORM

Cell Formulas
Range	Formula
J2:J3	J2	=TEXT([@[Start time]],"åååå-MM-DD")

 

[RoryA]

So it looks like your FILTER formulas should be:

=FILTER('S-FORM'!$I:$I,('S-FORM'!$F:$F="yellow")*('S-FORM'!$J:$J=TEXT(A3,"åååå-MM-DD")),"No results")

 

[Martinpetersson]

Thank you! Now it's working!

 

[RoryA]

Glad we could help.