formula not working
= 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 |
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. Micayla Bergen wrote: = 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 -- Dave Peterson |
yes i want to sum the numbers in the cells, not combine them. so i changed
the & to + but got a value error. "Dave Peterson" wrote: 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. Micayla Bergen wrote: = 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 -- 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. Micayla Bergen wrote: yes i want to sum the numbers in the cells, not combine them. so i changed the & to + but got a value error. "Dave Peterson" wrote: 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. Micayla Bergen wrote: = 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 -- Dave Peterson -- Dave Peterson |
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 "Dave Peterson" wrote: 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. Micayla Bergen wrote: yes i want to sum the numbers in the cells, not combine them. so i changed the & to + but got a value error. "Dave Peterson" wrote: 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. Micayla Bergen wrote: = 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 -- Dave Peterson -- Dave Peterson |
How about the sum(i12,j12) suggestion--do you get an error then?
Micayla Bergen wrote: 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 "Dave Peterson" wrote: 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. Micayla Bergen wrote: yes i want to sum the numbers in the cells, not combine them. so i changed the & to + but got a value error. "Dave Peterson" wrote: 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. Micayla Bergen wrote: = 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
yes
"Dave Peterson" wrote: How about the sum(i12,j12) suggestion--do you get an error then? Micayla Bergen wrote: 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 "Dave Peterson" wrote: 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. Micayla Bergen wrote: yes i want to sum the numbers in the cells, not combine them. so i changed the & to + but got a value error. "Dave Peterson" wrote: 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. Micayla Bergen wrote: = 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
What's in I12?
What's in J12? Micayla Bergen wrote: yes "Dave Peterson" wrote: How about the sum(i12,j12) suggestion--do you get an error then? Micayla Bergen wrote: 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 "Dave Peterson" wrote: 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. Micayla Bergen wrote: yes i want to sum the numbers in the cells, not combine them. so i changed the & to + but got a value error. "Dave Peterson" wrote: 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. Micayla Bergen wrote: = 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
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 "Dave Peterson" wrote: What's in I12? What's in J12? Micayla Bergen wrote: yes "Dave Peterson" wrote: How about the sum(i12,j12) suggestion--do you get an error then? Micayla Bergen wrote: 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 "Dave Peterson" wrote: 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. Micayla Bergen wrote: yes i want to sum the numbers in the cells, not combine them. so i changed the & to + but got a value error. "Dave Peterson" wrote: 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. Micayla Bergen wrote: = 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
What does I12 evaluate to?
What does J12 evaluate to? Micayla Bergen wrote: 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 "Dave Peterson" wrote: What's in I12? What's in J12? Micayla Bergen wrote: yes "Dave Peterson" wrote: How about the sum(i12,j12) suggestion--do you get an error then? Micayla Bergen wrote: 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 "Dave Peterson" wrote: 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. Micayla Bergen wrote: yes i want to sum the numbers in the cells, not combine them. so i changed the & to + but got a value error. "Dave Peterson" wrote: 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. Micayla Bergen wrote: = 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
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. "Dave Peterson" wrote: What does I12 evaluate to? What does J12 evaluate to? Micayla Bergen wrote: 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 "Dave Peterson" wrote: What's in I12? What's in J12? Micayla Bergen wrote: yes "Dave Peterson" wrote: How about the sum(i12,j12) suggestion--do you get an error then? Micayla Bergen wrote: 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 "Dave Peterson" wrote: 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. Micayla Bergen wrote: yes i want to sum the numbers in the cells, not combine them. so i changed the & to + but got a value error. "Dave Peterson" wrote: 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. Micayla Bergen wrote: = 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- 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 Bergen wrote: 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. "Dave Peterson" wrote: What does I12 evaluate to? What does J12 evaluate to? Micayla Bergen wrote: 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 "Dave Peterson" wrote: What's in I12? What's in J12? Micayla Bergen wrote: yes "Dave Peterson" wrote: How about the sum(i12,j12) suggestion--do you get an error then? Micayla Bergen wrote: 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 "Dave Peterson" wrote: 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. Micayla Bergen wrote: yes i want to sum the numbers in the cells, not combine them. so i changed the & to + but got a value error. "Dave Peterson" wrote: 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. Micayla Bergen wrote: = 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
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... "Dave Peterson" wrote: 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 Bergen wrote: 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. "Dave Peterson" wrote: What does I12 evaluate to? What does J12 evaluate to? Micayla Bergen wrote: 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 "Dave Peterson" wrote: What's in I12? What's in J12? Micayla Bergen wrote: yes "Dave Peterson" wrote: How about the sum(i12,j12) suggestion--do you get an error then? Micayla Bergen wrote: 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 "Dave Peterson" wrote: 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. Micayla Bergen wrote: yes i want to sum the numbers in the cells, not combine them. so i changed the & to + but got a value error. "Dave Peterson" wrote: 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. Micayla Bergen wrote: = 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
I would think it would be easier to go back to the original range and fix up
errors the =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 Bergen wrote: 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... "Dave Peterson" wrote: 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 Bergen wrote: 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. "Dave Peterson" wrote: What does I12 evaluate to? What does J12 evaluate to? Micayla Bergen wrote: 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 "Dave Peterson" wrote: What's in I12? What's in J12? Micayla Bergen wrote: yes "Dave Peterson" wrote: How about the sum(i12,j12) suggestion--do you get an error then? Micayla Bergen wrote: 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 "Dave Peterson" wrote: 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. Micayla Bergen wrote: yes i want to sum the numbers in the cells, not combine them. so i changed the & to + but got a value error. "Dave Peterson" wrote: 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. Micayla Bergen wrote: = 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
All times are GMT +1. The time now is 01:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com