sumif or sumifs formula which does not sum if one or more of the values is zero.

JoCil

New Member
Joined
Jan 18, 2024
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi, I am new to the forum, but have used several of the great solutions posted here in the past.

The attached screenshot illustrates my current challenge better than I can describe it, but I'll try :):
The first column contains assembled (complete) product codes.
The second column contains the components which goes into the making of the assembled products, and there can be two or more components per assembled product.
The third column looks up each component price from elsewhere.
The last column is a sumif formula which totals each component price for that completed product.

The problem is that if the component price (from elsewhere) is missing (zero), the assembled product price is incorrect, and should rather default to zero.

I mainly work with excel tables hence the current assembled price formula is:
=SUMIFS([ComponentPrice],[AssemblyCode],[@AssemblyCode])

I searched for a solution and there were some that are related, but not quite what I need.
Some solutions suggested using combined IF and COUNTIF functions to identify if zeros appear in the range, but this then cancels ALL the assembled price calculations, even if they are correct.

I also tried the following without success:
=SUMIFS([ComponentPrice],[AssemblyCode],[@AssemblyCode],"<>")
=SUMIFS([ComponentPrice],[AssemblyCode],[@AssemblyCode],">0")


Any suggestions, please?

(PS, the table is just a simplified explanation of the issue so please ignore the fact that the component price is duplicated per each component of an assembled product on the screenshot of the table)
 

Attachments

  • Screenshot 2024-01-18 170009.jpg
    Screenshot 2024-01-18 170009.jpg
    49.7 KB · Views: 20

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Try:

Book1
ABCD
1AssemblyCodeComponentCodeComponentPriceAssemblyPrice
2A1102132
3A230132
4B10
5B260
6C1200206
7C26206
8D1150335
9D251335
10D346335
11D488335
Sheet3
Cell Formulas
RangeFormula
D2:D11D2=IF(COUNTIFS([AssemblyCode],[@AssemblyCode],[ComponentPrice],">0")=COUNTIF([AssemblyCode],[@AssemblyCode]),SUMIFS([ComponentPrice],[AssemblyCode],[@AssemblyCode]),0)
 
Upvote 0
Welcome to the MrExcel board!
For the future, please consider XL2BB for your sample data so that helpers can easily copy it to test with. :)

A marginally shorter option that I think should also work for you.

JoCil.xlsm
ABCD
1AssemblyCodeComponentCodeComponentPriceAssemblyPrice
2A1$ 102.00$ 132.00
3A2$ 30.00$ 132.00
4B1$ -$ -
5B2$ 6.00$ -
6C1$ 200.00$ 206.00
7C2$ 6.00$ 206.00
8D1$ 150.00$ 335.00
9D2$ 51.00$ 335.00
10D3$ 46.00$ 335.00
11D4$ 88.00$ 335.00
Sheet1
Cell Formulas
RangeFormula
D2:D11D2=SUMIFS([ComponentPrice],[AssemblyCode],[@AssemblyCode])*(MINIFS([ComponentPrice],[AssemblyCode],[@AssemblyCode])>0)
 
Upvote 0
Solution
Another idea to consider:
EDIT: I should add...this assumes the missing prices are blanks, not 0's....so this may not work for you.
Book2
ABCD
1AssemblyCodeComponentCodeComponentPriceAssemblyPrice
2ap11
3bd22
4cq0
5cf30
6dr49
7dg59
8es630
9et730
10ex830
11ey930
Sheet3
Cell Formulas
RangeFormula
D2:D11D2=LET(a,FILTER([ComponentPrice],[AssemblyCode]=@[AssemblyCode]),IF(XOR(ISBLANK(a)),0,SUM(a)))

Similar version that accounts for 0's:
Book2
ABCD
1AssemblyCodeComponentCodeComponentPriceAssemblyPrice
2A1102132
3A230132
4B100
5B260
6C1200206
7C26206
8D1150335
9D251335
10D346335
11D488335
Sheet4
Cell Formulas
RangeFormula
D2:D11D2=LET(a,FILTER([ComponentPrice],[AssemblyCode]=@[AssemblyCode]),IF(XOR(a>0),0,SUM(a)))
 
Last edited:
Upvote 0
Hi Peter, the value in C4 is undoubtably 0, based on the original formatting. And your formula works fine if that's the case. But I noticed that if that cell is empty "" , then your formula fails, which is why I went with the longer version. Probably not an issue though.

KRice, your formula has the opposite issue. If the value is 0, it fails. Moreover, if 2 components are empty (or any even number), then it also fails. But an interesting approach!
 
Upvote 0
Thanks,..good point, Eric!...and my 2nd quick edit fails too. Hmmm...
 
Upvote 0
Thanks,..good point, Eric!...and my 2nd quick edit fails too. Hmmm...
Maybe:

Book1
ABCD
1AssemblyCodeComponentCode ComponentPrice AssemblyPrice
2A1$ 102.00132
3A2$ 30.00132
4B1$ -0
5B2$ 6.000
6C1$ 200.00206
7C2$ 6.00206
8D1$ 150.000
9D20
10D3$ 46.000
11D40
Sheet3
Cell Formulas
RangeFormula
D2:D11D2=LET(a,FILTER([ComponentPrice],[AssemblyCode]=@[AssemblyCode]),IF(OR(a=0),0,SUM(a)))
 
Upvote 0
Eric, yes, that's a good adjustment, and it handles 0's and blanks...and multiple 0's and blanks. Thank you.
 
Upvote 0
Hi Peter, the value in C4 is undoubtably 0, based on the original formatting. And your formula works fine if that's the case. But I noticed that if that cell is empty "" ,
Hi Eric. Apart from the formatting indication, my reading of it was that we were told that it was zero
The problem is that if the component price (from elsewhere) is missing (zero), ..
 
Upvote 0
Try:

Book1
ABCD
1AssemblyCodeComponentCodeComponentPriceAssemblyPrice
2A1102132
3A230132
4B10
5B260
6C1200206
7C26206
8D1150335
9D251335
10D346335
11D488335
Sheet3
Cell Formulas
RangeFormula
D2:D11D2=IF(COUNTIFS([AssemblyCode],[@AssemblyCode],[ComponentPrice],">0")=COUNTIF([AssemblyCode],[@AssemblyCode]),SUMIFS([ComponentPrice],[AssemblyCode],[@AssemblyCode]),0)
Hi Eric,
This does the job, thank you VERY much !
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top