help with sumifs multiple Sheets

[Lukma]

Hi friend

good day, I need help with a few issues on my formula

I have used SUMIFS in my data summary sheet to sum multiple sheets In columns C, D, E, and G, H, I, but the problem I am having now is that where there is "zero" I need it to be a blank cell
Also, I don't know if my formula in column J is right as I need to sum Column G & H and then Subtract Column I to have the balance. in column J

Appreciate support from the team

Thanks & Regards

Weekly Dispatch CCU Report July 2023.xlsx
	B	C	D	E	F	G	H	I	J
3	Date	Outstanding CCU from previous day	New CCU's ready for shipment for the day	Dispatched CCU's for the day	Balance CCU's (end of the day)	Outstanding loose cargo from previous day	New cargo ready for shipment for the day	Dispatched loose cargo for the day	Balance Loose cargo (end of the day)
4	01-Jul	660	5	29	636	443	0	0	443
CCU Summary Report

Cell Formulas
Range	Formula
B4	B4	=IFERROR(VLOOKUP(CHEMICAL!C4,CHEMICAL!C4:C36,1,0),"")
C4:E4,H4:I4	C4	=SUMIFS(CHEMICAL!D$4:D$60000,CHEMICAL!$C$4:$C$60000,'CCU Summary Report'!$B4)+SUMIFS('M&H'!C$4:C$60000,'M&H'!$B$4:$B$60000,'CCU Summary Report'!$B4)
F4	F4	=IFERROR(1/(1/SUM(C4:D4))-E4,"")
G4	G4	=IFERROR(SUMIFS(CHEMICAL!H$4:H$60000,CHEMICAL!$C$4:$C$60000,'CCU Summary Report'!$B4)+SUMIFS('M&H'!G$4:G$60000,'M&H'!$B$4:$B$60000,'CCU Summary Report'!$B4),"")
J4	J4	=IFERROR(1/(1/SUM(G4+H4))-I4,"-")

 

[RobP]

Hi,

would you not be able to wrap it (C4) in an IF statement like so :

=IF(SUMIFS(CHEMICAL!D$4:D$60000,CHEMICAL!$C$4:$C$60000,'CCU Summary Report'!$B4)+SUMIFS('M&H'!C$4:C$60000,'M&H'!$B$4:$B$60000,'CCU Summary Report'!$B4)=0,"",SUMIFS(CHEMICAL!D$4:D$60000,CHEMICAL!$C$4:$C$60000,'CCU Summary Report'!$B4)+SUMIFS('M&H'!C$4:C$60000,'M&H'!$B$4:$B$60000,'CCU Summary Report'!$B4))

 

[Fluff]

Another option would be

=IFERROR(1/(1/SUMIFS(CHEMICAL!D$4:D$60000,CHEMICAL!$C$4:$C$60000,'CCU Summary Report'!$B4)+SUMIFS('M&H'!C$4:C$60000,'M&H'!$B$4:$B$60000,'CCU Summary Report'!$B4)),"")

 

[Lukma]

Hi,

would you not be able to wrap it (C4) in an IF statement like so :

=IF(SUMIFS(CHEMICAL!D$4:D$60000,CHEMICAL!$C$4:$C$60000,'CCU Summary Report'!$B4)+SUMIFS('M&H'!C$4:C$60000,'M&H'!$B$4:$B$60000,'CCU Summary Report'!$B4)=0,"",SUMIFS(CHEMICAL!D$4:D$60000,CHEMICAL!$C$4:$C$60000,'CCU Summary Report'!$B4)+SUMIFS('M&H'!C$4:C$60000,'M&H'!$B$4:$B$60000,'CCU Summary Report'!$B4))

Hi Robp / Fluff

Thank you so much for being there always to support both formulas really resolved my issue, just a quick one
After inserting the formula I decided that instead of Keeping a blank cell I use a negative sign (-) to replace the blank in the data just to keep it clean.

My apology please I would love to have a solution with the column J formula instead of having zero I need to have a blank where there is not value

Appreciate the support.

Regards

 

[RobP]

Hi,

you can just insert a (-) sign in between the "" within the formula that you used I believe. Therefore if its a zero, it will use a (-) instead. So should look like "-" instead of ""

cheers
Rob

 

[Lukma]

Hi,

you can just insert a (-) sign in between the "" within the formula that you used I believe. Therefore if its a zero, it will use a (-) instead. So should look like "-" instead of ""

cheers
Rob

Dear Robp

Yes, I did just that. however in Column J with formula i need

=IFERROR(1/(1/SUM(I40+J40))-K40,"-")

IF Column G + Column H =703 then Subtract Column I then result in Column J should be 609 but instead am getting Negative sign but if i take out the Negative sign from my Formula i will have right answer but because i need to drag down all formula,

Column G	Column H	Column I	Column J Balance
703	-	94	- Error using Formula Above when drag down answer should be 609
378	18	152	244
-	-	-	-
-	-	-	-

How can i update it Please appreciate the help of formula in column J

regards

 

[RobP]

yes, its because you are trying to add / subtract a "character" = (-) rather than a number. Excel cannot add 1+2+"-", or 1+2+"A" for example. So best you go back to leave it as 0, or a blank (as it can interpret a blank as 0)

Rgds
Rob

 

[Lukma]

yes, its because you are trying to add / subtract a "character" = (-) rather than a number. Excel cannot add 1+2+"-", or 1+2+"A" for example. So best you go back to leave it as 0, or a blank (as it can interpret a blank as 0)

Rgds
Rob

Dear Robp

Many Thanks i will just do that

Regards