formula not working

M

Micayla Bergen

= SUM('A & E John Super'!$P$4:$P$39)/SUM('A & E John Super'!$L$4:$Q$39)
where =IF(ISERROR((VLOOKUP(A12,'share detail'!$D$2:$D$52,1,
FALSE))),"",I12&J12) is on the A & E John Super worksheet
there are values on the AEJohn worksheet but they are not adding up on the
other worksheet. do i need another reference or ' or something?
thanks
 
D

Dave Peterson

This formula returns text:

=IF(ISERROR((VLOOKUP(A12,'share detail'!$D$2:$D$52,1,FALSE))),"",I12&J12)

Did you mean i12+j12 (to sum those numbers).

If i12=123 and j12=456, then i12&j12 = 123456. But i12+j12=579.
 
M

Micayla Bergen

yes i want to sum the numbers in the cells, not combine them. so i changed
the & to + but got a value error.
 
D

Dave Peterson

Do you get the error when you put:
=i12+j12
in a cell by itself (just for testing purposes).

For this expression to work, both I12 and J12 have to be numeric (or empty).

If there's a chance you have text in those cells, maybe:

=IF(ISERROR((VLOOKUP(A12,'share detail'!$D$2:$D$52,1,FALSE))),"",sum(I12,J12))

would work better.

===
Be aware that if either I12 or J12 has an error in it, then you'll get an error
returned for the sum.
 
M

Micayla Bergen

only one cell will have a value because it denotes which type of product it
is, but im not sure how to say look in either one so ive said both.

yes i get the error when by itself
 
D

Dave Peterson

How about the sum(i12,j12) suggestion--do you get an error then?

Micayla said:
only one cell will have a value because it denotes which type of product it
is, but im not sure how to say look in either one so ive said both.

yes i get the error when by itself
Click to expand...
 
M

Micayla Bergen

I12is =IF(G4=0,H4*D4, "")
J12 is =IF(G4=1,H4*D4, "")
G4 is =IF(ISERROR((VLOOKUP(A4,'Model Portfolio'!$C$6:$C$50,1, FALSE))),0,1)
H4 is a vlookup
D4 is a manually input number
 
D

Dave Peterson

What does I12 evaluate to?
What does J12 evaluate to?



Micayla said:
I12is =IF(G4=0,H4*D4, "")
J12 is =IF(G4=1,H4*D4, "")
G4 is =IF(ISERROR((VLOOKUP(A4,'Model Portfolio'!$C$6:$C$50,1, FALSE))),0,1)
H4 is a vlookup
D4 is a manually input number
Click to expand...
 
M

Micayla Bergen

in row A I12 is $10821.00, J12 is nothing, in row B I12 is nothing and J12 is
5430 but there is no pattern to which is blank on which row.
 
D

Dave Peterson

I'm confused.

I12 is a in row 12 in column I. J12 is in row 12 column J.

I don't see how
=sum(i12,j12)
could return an error

Micayla said:
in row A I12 is $10821.00, J12 is nothing, in row B I12 is nothing and J12 is
5430 but there is no pattern to which is blank on which row.
Click to expand...
 
M

Micayla Bergen

Ok so the value of I12 and J12 is put into columns L M O P or Q based on what
classification it has, but when i try to sum the values in those columns it
comes up as an error.
on the summary sheet i have = SUM('A & E John Super'!$L$4:$L$39)/SUM('A & E
John Super'!$L$4:$Q$39)
and this is returning the error. i dont know if its because of the format of
the values on the sheet or something...
 
D

Dave Peterson

I would think it would be easier to go back to the original range and fix up
errors there:

=if(iserror(SUM('A & E John Super'!$L$4:$L$39)
/SUM('A & E John Super'!$L$4:$Q$39)),"Error",
SUM('A & E John Super'!$L$4:$L$39)
/SUM('A & E John Super'!$L$4:$Q$39))

If you use =sum(somerange) and that somerange contains an error, you'll get an
error back.



Micayla said:
Ok so the value of I12 and J12 is put into columns L M O P or Q based on what
classification it has, but when i try to sum the values in those columns it
comes up as an error.
on the summary sheet i have = SUM('A & E John Super'!$L$4:$L$39)/SUM('A & E
John Super'!$L$4:$Q$39)
and this is returning the error. i dont know if its because of the format of
the values on the sheet or something...
Click to expand...
 
Top