Invalid formula b/c of parentheses?

[brooksc29]

I've been staring at this and can't figure out where the error is. It's saying the formula is invalid, could someone take a look and see if the parentheses are all making sense?

thank you!


=IFERROR("SUMIF(B$3:B3,GRAPH!AF$3,(((OPPERA!R$3:R3)*13)+((OPPERA!O$3:O3+OPPERA!S$3:S3-OPPERA!T$3:T3)*3)-((OPPERA!U$3:U3)*2)))/(SUMIF(B$3:B3,GRAPH!AF$3,OPPERA!N$3:N3))", 0)

 

[VoG]

You need to delete the quotes marked in red

=IFERROR("SUMIF(B$3:B3,GRAPH!AF$3,(((OPPERA!R$3:R3)*13)+((OPPERA!O$3:O3+OPPERA!S$3:S3-OPPERA!T$3:T3)*3)-((OPPERA!U$3:U3)*2)))/(SUMIF(B$3:B3,GRAPH!AF$3,OPPERA!N$3:N3))", 0)

 

[brooksc29]

i did that and it's still invalid. the quotes were just excel entering them after i clicked out of the cell with the invalid formula still in it

 

[brooksc29]

do i need the second SUM IF? really it should be already qualified as to what I am looking for.

I would need the part after the division sign to have the same qualifications as the first SUM IF

 

[T. Valko]

I've been staring at this and can't figure out where the error is. It's saying the formula is invalid, could someone take a look and see if the parentheses are all making sense?

thank you!


=IFERROR("SUMIF(B$3:B3,GRAPH!AF$3,(((OPPERA!R$3:R3)*13)+((OPPERA!O$3:O3+OPPERA!S$3:S3-OPPERA!T$3:T3)*3)-((OPPERA!U$3:U3)*2)))/(SUMIF(B$3:B3,GRAPH!AF$3,OPPERA!N$3:N3))", 0)

Aside from the quotes that need to be removed, what is the sum_range?

The arguments for SUMIF are:

Criteria_range
Criteria
Sum_range

In the first SUMIF function your sum_range argument is not valid.

(((OPPERA!R$3:R3)*13)+((OPPERA!O$3:O3+OPPERA!S$3:S3-OPPERA!T$3:T3)*3)-((OPPERA!U$3:U3)*2))

 

[shg]

Maybe this?

=IFERROR(SUMPRODUCT( (B$3:B3 = GRAPH!AF$3) * (13*OPPERA!R$3:R3 + 3*(OPPERA!O$3:O3 + OPPERA!S$3:S3 - OPPERA!T$3:T3) - 2 * OPPERA!U$3:U3)) / SUMIF(B$3:B3, GRAPH!AF$3, OPPERA!N$3:N3), 0)

 

[brooksc29]

the sumproduct formula gives me an incorrect value, but i also forgot one piece.

right now I have this

=3.11+ IFERROR((((SUMIF(B$3:B7,GRAPH!AF$3,OPPERA!$R7:R7)*13)+((SUMIF(B$3:B7,GRAPH!AF$3,OPPERA!$S7:S7) + SUMIF(B$3:B7,GRAPH!AF$3,OPPHBP!$P7:P7)-SUMIF(B$3:B7,GRAPH!AF$3,OPPERA!$T7:T7)*3)-(SUMIF(B$3:B7,GRAPH!AF$3,OPPERA!$U7:U7)*2)))/(SUMIF(B$3:B7,GRAPH!AF$3,OPPERA!$N7:N7))), 0)

and the result is -.53 when the result should be 10.96.

too many parentheses?

 

[brooksc29]

i had the $ sign in front of the column, instead of the row.

the result is still off though, I'm getting 9.54