If >=0 and sumif or sumproduct

**Jerseynjphillypa** · 10-18-2011, 10:14 AM

I am trying to sum Amount Ordered and Extra together and sum stock and gone together. But if any of the columns have no value, I don't want it to do the calculation. I keep getting an error and I don't know how to fix it.

But eventually I am trying to write a function in one line in sheet 2 under Traffic so I can assign a conditional formatting.

So if Sum of Stock and Gone is greater or equal to Sum of Amount Order and Extra it should be 2 or yellow

=If(Inventory!$E$2:$E$7,Inventory!$A$2:$A$7,Sheet2!$A7,Inventory!$B$2:$B$7,Sheet2!$B7),(Inventory!$F $2:$F$7,Inventory!$A$2:$A$7,Sheet2!$A7,Inventory!$B$2:$B$7,Sheet2!$B7),">=0"),SUM(SUMIFS(Inventory!$ E$2:$E$7,Inventory!$A$2:$A$7,Sheet2!$A7,Inventory!$B$2:$B$7,Sheet2!$B7),SUMIFS(Inventory!$F$2:$F$7,I nventory!$A$2:$A$7,Sheet2!$A7,Inventory!$B$2:$B$7,Sheet2!$B7),3,2)

**NBVC** · 10-18-2011, 10:53 AM

If I understand correctly, you don't want to sum the 2 columns if either is blank... so try:

=SUMPRODUCT(--(Inventory!$C$2:$C$7<>""),--(Inventory!$D$2:$D$7<>""),--(Inventory!$A$2:$A$7=A2),--(Inventory!$B$2:$B$7=B2),Inventory!$C$2:$C$7+Inventory!$D$2:$D$7)

**Jerseynjphillypa** · 10-18-2011, 03:02 PM

Thanks for your help. Your assumption was correct, but I still got a value of 0, it should be blank. Would I use "" at the end of the function

**NBVC** · 10-18-2011, 03:12 PM

The best way to get rid of the 0 is to hide it by formatting the result cells as Custom: 0;-0;;@

**Jerseynjphillypa** · 10-20-2011, 09:14 AM

My problem is sometimes 0 can be a valid answer, so if I edit the custom formatting the 0 would not appear

**NBVC** · 10-20-2011, 10:08 AM

Try:

=IF(AND(INDEX(Inventory!$C$2:$C$7,MATCH(1,INDEX((Inventory!$A$2:$A$7=A2)*(Inventory!$B$2:$B$7=B2),0),0))="",INDEX(Inventory!$D$2:$D$7,MATCH(1,INDEX((Inventory!$A$2:$A$7=A2)*(Inventory!$B$2:$B$7=B2),0),0))=""),"",SUMPRODUCT(--(Inventory!$C$2:$C$7<>""),--(Inventory!$D$2:$D$7<>""),--(Inventory!$A$2:$A$7=A2),--(Inventory!$B$2:$B$7=B2),Inventory!$C$2:$C$7+Inventory!$D$2:$D$7))

**Jerseynjphillypa** · 10-20-2011, 12:51 PM

That's perfect,, how would I change the code, if they provide me a value in one of the columns out of the two to find the sum.

**NBVC** · 10-20-2011, 02:08 PM

Perhaps?

=IF(AND(INDEX(Inventory!$C$2:$C$7,MATCH(1,INDEX((Inventory!$A$2:$A$7=A2)*(Inventory!$B$2:$B$7=B2),0),0))="",INDEX(Inventory!$D$2:$D$7,MATCH(1,INDEX((Inventory!$A$2:$A$7=A2)*(Inventory!$B$2:$B$7=B2),0),0))=""),"",SUMPRODUCT(--(Inventory!$A$2:$A$7=A2),--(Inventory!$B$2:$B$7=B2),Inventory!$C$2:$C$7)+SUMPRODUCT(--(Inventory!$A$2:$A$7=A2),--(Inventory!$B$2:$B$7=B2),Inventory!$D$2:$D$7))

**Jerseynjphillypa** · 10-20-2011, 02:19 PM

What if there is text in the field. How do I prevent it from saying 0 since that's not a valid value for text. I

I'm using this formula to sum up 4 columns together, two columns together and then comparing their values against one another. How can I run the if statement only if there are sums to add up that is greater or equal to 0.