SUM PRODUCT error when cell has formula?

[brainzlp]

Hello all

I need to do the same i did for Quantity (quantidade in Portuguese)
To "Metros Lineares", although with the formula applied it gives me error "VALUE"

Please check the attach.

Once i put the colors in "ORDEM TRABALHO ENVELOPES", it recognizes the color and put the sum of that color in another sheet place in the same color column.

The formula i have was given from a member of this community, hope he can participate again.

Hope you can help me out with this.
Any information you need let me know.

[mcmahobt]

I'm not exactly sure what you're trying to accomplish, but maybe something like this in A5?:

A5:

=IF(--ISNUMBER(SUMPRODUCT(('ORDEM TRABALHO ENVELOPES'!$R$53:$AA$54=$A$1)*('ORDEM TRABALHO ENVELOPES'!$R$55:$AA$55=A4)*'ORDEM TRABALHO ENVELOPES'!$R$59:$AA$59))=0,"",SUMPRODUCT(('ORDEM TRABALHO ENVELOPES'!$R$53:$AA$54=$A$1)*('ORDEM TRABALHO ENVELOPES'!$R$55:$AA$55=A4)*'ORDEM TRABALHO ENVELOPES'!$R$59:$AA$59))

[brainzlp]

I'm not exactly sure what you're trying to accomplish, but maybe something like this in A5?:

A5:

=IF(--ISNUMBER(SUMPRODUCT(('ORDEM TRABALHO ENVELOPES'!$R$53:$AA$54=$A$1)*('ORDEM TRABALHO ENVELOPES'!$R$55:$AA$55=A4)*'ORDEM TRABALHO ENVELOPES'!$R$59:$AA$59))=0,"",SUMPRODUCT(('ORDEM TRABALHO ENVELOPES'!$R$53:$AA$54=$A$1)*('ORDEM TRABALHO ENVELOPES'!$R$55:$AA$55=A4)*'ORDEM TRABALHO ENVELOPES'!$R$59:$AA$59))

In a5, This works perfectly, althouh don't know how...
=IFERROR(1/(1/SUMPRODUCT(('ORDEM TRABALHO ENVELOPES'!$R$53:$AA$53=$A$1)*('ORDEM TRABALHO ENVELOPES'!$R$55:$AA$55=A3),'ORDEM TRABALHO ENVELOPES'!$R$59:$AA$59)),"")

Thanks for you help but it's too complex. I just still need help for the formula in "OUTRAS" Which is different

[Jonmo1]

Try

=IFERROR(1/(1/SUMPRODUCT(('ORDEM TRABALHO ENVELOPES'!$R$53:$AA$53=$A$1)*('ORDEM TRABALHO ENVELOPES'!$R$55:$AA$55=A3),'ORDEM TRABALHO ENVELOPES'!$R$59:$AA$59)),"")

[brainzlp]

Try

=IFERROR(1/(1/SUMPRODUCT(('ORDEM TRABALHO ENVELOPES'!$R$53:$AA$53=$A$1)*('ORDEM TRABALHO ENVELOPES'!$R$55:$AA$55=A3),'ORDEM TRABALHO ENVELOPES'!$R$59:$AA$59)),"")

Thanks, it works perfectly.

I need to fill all fields with the formula.

Just need your help to fill the "OUTRAS" Field, which has a different formula.

It means that, if it finds the header ("PET TRANSPARENT") sum that quantity minus the colors found.

Hope you can understand with the example:

=SOMARPRODUTO(('ORDEM TRABALHO ENVELOPES'!$R$53:$AA$54=$A$1)*'ORDEM TRABALHO ENVELOPES'!$R$57:$AA$57)-SOMA(A4:U4))

ALthough need this with your formula, if possible explain me what is the 1/1 etc for.. how does that formula works

[Jonmo1]

You had 2 basica problems.

1, 'ORDEM TRABALHO ENVELOPES'!$R$53:$AA$54
On that sheet, rows 53 and 54 are a merged range, so you should only look at 53
'ORDEM TRABALHO ENVELOPES'!$R$53:$AA$53

2, when you multiplied by the values in row 59, those were actually formulas and some returned ""
"" is NOT blank, it's a text string
Number*Text = #Value! error.

By changing that last * to a comma, that forced sumproduct to process that range, and it's able to ingore text values that way.


Then I was able to cut the formula in half with a neat little math trick.

Instead of writing IF(Number=0,"",Number)
You can do IFERROR(1/(1/Number),"")
Take any number, say 10 for example
1/10 = 0.10
Then
1/0.10 = 10

So any number, that little math trick will always return the same number
Unless it's a 0.
If it's 0, you get #Div/0! error
That's where the iferror handles it.

Hope that helps.

[Jonmo1]

Try

=SOMARPRODUTO(('ORDEM TRABALHO ENVELOPES'!$R$53:$AA$53=$A$1),'ORDEM TRABALHO ENVELOPES'!$R$57:$AA$57)-SOMA(A4:U4))

[brainzlp]

Try

=SOMARPRODUTO(('ORDEM TRABALHO ENVELOPES'!$R$53:$AA$53=$A$1),'ORDEM TRABALHO ENVELOPES'!$R$57:$AA$57)-SOMA(A4:U4))

There is no way to use a formula without unmerge the Cell?

Something like what i had earlier but for the second row:

SOMARPRODUTO(('ORDEM TRABALHO ENVELOPES'!$R$53:$AA$54=$A$1)*'ORDEM TRABALHO ENVELOPES'!$R$59:$AA$59)-SOMA(A5:U5))

[Jonmo1]

There is no way to use a formula without unmerge the Cell?

No one said you had to unmerge the cell.

Just adjust the formula to only refer to the top cell of the merged ranges.

[brainzlp]

No one said you had to unmerge the cell.

Just adjust the formula to only refer to the top cell of the merged ranges.

Hello, The formula you told me, doesn't do what i exactly need.

Let me know what is the formula to give me TOTAL of "PET TRANSPARENT" , don't matter the color, i just need the total.

I can also edit the actual formula that works:

=IFERROR(1/(1/SUMPRODUCT(('ORDEM TRABALHO ENVELOPES'!$R$53:$AA$53=$A$1)*('ORDEM TRABALHO ENVELOPES'!$R$55:$AA$55=A3),'ORDEM TRABALHO ENVELOPES'!$R$59:$AA$59)),"")

And instead of look for COLOR, look in the CODE for "Sem Código" (English - Without Code)

[brainzlp]

Any suggestion of how can i do it for the colors that have no code?