Formula SUMIFS with value in different column

[Lukma]

Hi Friend

i need an assistance with the SUMIFS, but i don't no if i get it all right, i need the zero to be blank i tries IFERROR(1/(1/SUMIFS, but each giving me pop up error message appreciate a solution with it or a better way to have the formula
Thanks

Book7.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
4	May	220	265	266.666	267	4.55747	5.329922	7.399186	4.057618
5	Location	Water	Diesel	Jet A-1	M.Spirit	CaCl2	NaCl	NAF	Base Oil	Barite	Bentonite	Class"G"	Expendable	Flex.St	Elasticem	Lite Crete	Lite Deep	Silica	Tuned Light	Neocem
6	Rig Al Yasat	0	0	0	0	0	0	420.8571
7
8	OUTVOUND											OUTVOUND
9	Month	Date	Name	MUS		Location	Cargo Type		QTY			Month	Date	Name	MUS		Location	Cargo Type		QTY
10	May	14/05/22 04:00	ADNOC-229	1194	NAF	Rig Al Yasat	NAF	784 BBLS RDF NAF	784			May	14/05/22 04:00	ADNOC-229	1194	NAF	Rig Al Yasat	NAF	784 BBLS RDF NAF	784
11	May	21/05/22 00:20	ADNOC-229	1255	NAF	Rig Al Yasat	NAF	RDF NAF (12.1 ppg) 773 BBLS NAF TANK# NAF- 12708921, 730247	773			May	21/05/22 00:20	ADNOC-229	1255	NAF	Rig Al Yasat	NAF	RDF NAF (12.1 ppg) 773 BBLS NAF TANK# NAF- 12708921, 730247	773
Sheet1

Cell Formulas
Range	Formula
C6:I6	C6	=SUMIFS($J$10:$J$11,$G$10:$G$11,$B6,$B$10:$B$11,$B$4,$F$10:$F$11,C$5)/C$4+SUMIFS($U$10:$U$11,$R$10:$R$11,$B6,$M$10:$M$11,$B$4,$S$10:$S$11,C$5)/C$4

 

[etaf]

this may help
=IF(SUMIFS($J$10:$J$11,$G$10:$G$11,$B6,$B$10:$B$11,$B$4,$F$10:$F$11,C$5)/C$4+SUMIFS($U$10:$U$11,$R$10:$R$11,$B6,$M$10:$M$11,$B$4,$S$10:$S$11,C$5)/C$4=0,"",SUMIFS($J$10:$J$11,$G$10:$G$11,$B6,$B$10:$B$11,$B$4,$F$10:$F$11,C$5)/C$4+SUMIFS($U$10:$U$11,$R$10:$R$11,$B6,$M$10:$M$11,$B$4,$S$10:$S$11,C$5)/C$4)

maybe otherways - but that was the first i thought of

you can set up excel to NOT show zeros - but this would apply to the entire sheet and may not be what you want

you could use conditional formating , so the zero font is white and then blends in with the fill white colour and so cannot be seen - but will still be a zero - if tested by another formula

 

[Peter_SSs]

Another option is to keep your original formula and format those formula cells with the custom format: General;General;;

22 05 30.xlsm
	B	C	D	E	F	G	H	I	J
5	Location	Water	Diesel	Jet A-1	M.Spirit	CaCl2	NaCl	NAF	Base Oil
6	Rig Al Yasat							420.857105
Custom Format

Cell Formulas
Range	Formula
C6:I6	C6	=SUMIFS($J$10:$J$11,$G$10:$G$11,$B6,$B$10:$B$11,$B$4,$F$10:$F$11,C$5)/C$4+SUMIFS($U$10:$U$11,$R$10:$R$11,$B6,$M$10:$M$11,$B$4,$S$10:$S$11,C$5)/C$4