Adding together numeric values based on If Formula result

[J_Stan]

Hopefully one of you amazing pros can help me resolve an issue with a nested IF Formula that isn’t quite doing what I hoped it would.

I have four individual products (Line Item #1 through to Line Item #4) and if they are ‘Espresso Blend’ I’d then like their total weight {Total Coffee Weight (G)} to be added together to give me the total amount of Espresso Blend across all of the products combined.

For reasons I can’t quite understand the below formula isn’t very happy when the Line Item isn’t Espresso Blend. Rather than throwing the desired ‘0’ into the calculation it is cancelling out everything but the first item to include Espresso Blend.

Any help would be greatly appreciated!

IF({Line Item #1 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #1 Quantity},0
+
IF({Line Item #2 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #2 Quantity},0
+
IF({Line Item #3 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #3 Quantity},0
+
IF({Line Item #4 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #4 Quantity},0

))))

[Chris-T · Accepted solution]

I think you need to sum the IFs as individual statements, not nested.

IF({Line Item #1 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #1 Quantity},0)
+
IF({Line Item #2 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #2 Quantity},0)
+
IF({Line Item #3 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #3 Quantity},0)
+
IF({Line Item #4 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #4 Quantity},0)

Of course, look out for your double quotes if you copy this back into your formula directly.

[J_Stan]

Chris-T:

IF({Line Item #1 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #1 Quantity},0)
+
IF({Line Item #2 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #2 Quantity},0)
+
IF({Line Item #3 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #3 Quantity},0)
+
IF({Line Item #4 Coffee}=“Espresso Blend”, {Total Coffee Weight (G): Line Item #4 Quantity},0)

Works perfectly! Thanks @Chris-T - I thought it would be something straightforward that I was missing. Appreciate the help.