|
SUMIF function referring to values on different Worksheet
I want to enter a formula on a different worksheet that contains a SUMIF
function for values on another sheet. The formula has a nested SUMIF that works fine on the same sheet, but when I try to enter the formula on a different sheet, the results are wrong or I get an error. Can't this be done? |
SUMIF referring to another sheet is no problem. Maybe the nested SUMIF is
causing a problem. Show the data and the formula. -- HTH RP (remove nothere from the email address if mailing direct) "TeeBee0831" wrote in message ... I want to enter a formula on a different worksheet that contains a SUMIF function for values on another sheet. The formula has a nested SUMIF that works fine on the same sheet, but when I try to enter the formula on a different sheet, the results are wrong or I get an error. Can't this be done? |
=SUMIF('Query-Cost Centers'!A26:A116,"<1000000",'Query-Cost
Centers'!C26:C116)+SUMIF('Query-Cost Centers'!A26:A116,"899999",'Query-Cost Centers'!C26:C116) I get a result of 0 which is wrong. "Bob Phillips" wrote: SUMIF referring to another sheet is no problem. Maybe the nested SUMIF is causing a problem. Show the data and the formula. -- HTH RP (remove nothere from the email address if mailing direct) "TeeBee0831" wrote in message ... I want to enter a formula on a different worksheet that contains a SUMIF function for values on another sheet. The formula has a nested SUMIF that works fine on the same sheet, but when I try to enter the formula on a different sheet, the results are wrong or I get an error. Can't this be done? |
I just created some data and tested that formula and it works for me.
It does seem odd though, as it double-counts the items between 899,999 and 100,000 because those numbers satisfy both tests Is this perhaps what you want =SUMPRODUCT(--('Query-Cost Centers'!A26:A116<1000000),--('Query-Cost Centers'!A26:A116899999),'Query-Cost Centers'!C26:C116) -- HTH RP (remove nothere from the email address if mailing direct) "teebee0831" wrote in message ... =SUMIF('Query-Cost Centers'!A26:A116,"<1000000",'Query-Cost Centers'!C26:C116)+SUMIF('Query-Cost Centers'!A26:A116,"899999",'Query-Cost Centers'!C26:C116) I get a result of 0 which is wrong. "Bob Phillips" wrote: SUMIF referring to another sheet is no problem. Maybe the nested SUMIF is causing a problem. Show the data and the formula. -- HTH RP (remove nothere from the email address if mailing direct) "TeeBee0831" wrote in message ... I want to enter a formula on a different worksheet that contains a SUMIF function for values on another sheet. The formula has a nested SUMIF that works fine on the same sheet, but when I try to enter the formula on a different sheet, the results are wrong or I get an error. Can't this be done? |
Copying this formula gave me a REF error.
"Bob Phillips" wrote: I just created some data and tested that formula and it works for me. It does seem odd though, as it double-counts the items between 899,999 and 100,000 because those numbers satisfy both tests Is this perhaps what you want =SUMPRODUCT(--('Query-Cost Centers'!A26:A116<1000000),--('Query-Cost Centers'!A26:A116899999),'Query-Cost Centers'!C26:C116) -- HTH RP (remove nothere from the email address if mailing direct) "teebee0831" wrote in message ... =SUMIF('Query-Cost Centers'!A26:A116,"<1000000",'Query-Cost Centers'!C26:C116)+SUMIF('Query-Cost Centers'!A26:A116,"899999",'Query-Cost Centers'!C26:C116) I get a result of 0 which is wrong. "Bob Phillips" wrote: SUMIF referring to another sheet is no problem. Maybe the nested SUMIF is causing a problem. Show the data and the formula. -- HTH RP (remove nothere from the email address if mailing direct) "TeeBee0831" wrote in message ... I want to enter a formula on a different worksheet that contains a SUMIF function for values on another sheet. The formula has a nested SUMIF that works fine on the same sheet, but when I try to enter the formula on a different sheet, the results are wrong or I get an error. Can't this be done? |
Did you fix the wrap-around.
-- HTH RP (remove nothere from the email address if mailing direct) "teebee0831" wrote in message ... Copying this formula gave me a REF error. "Bob Phillips" wrote: I just created some data and tested that formula and it works for me. It does seem odd though, as it double-counts the items between 899,999 and 100,000 because those numbers satisfy both tests Is this perhaps what you want =SUMPRODUCT(--('Query-Cost Centers'!A26:A116<1000000),--('Query-Cost Centers'!A26:A116899999),'Query-Cost Centers'!C26:C116) -- HTH RP (remove nothere from the email address if mailing direct) "teebee0831" wrote in message ... =SUMIF('Query-Cost Centers'!A26:A116,"<1000000",'Query-Cost Centers'!C26:C116)+SUMIF('Query-Cost Centers'!A26:A116,"899999",'Query-Cost Centers'!C26:C116) I get a result of 0 which is wrong. "Bob Phillips" wrote: SUMIF referring to another sheet is no problem. Maybe the nested SUMIF is causing a problem. Show the data and the formula. -- HTH RP (remove nothere from the email address if mailing direct) "TeeBee0831" wrote in message ... I want to enter a formula on a different worksheet that contains a SUMIF function for values on another sheet. The formula has a nested SUMIF that works fine on the same sheet, but when I try to enter the formula on a different sheet, the results are wrong or I get an error. Can't this be done? |
Wrap around?
"Bob Phillips" wrote: Did you fix the wrap-around. -- HTH RP (remove nothere from the email address if mailing direct) "teebee0831" wrote in message ... Copying this formula gave me a REF error. "Bob Phillips" wrote: I just created some data and tested that formula and it works for me. It does seem odd though, as it double-counts the items between 899,999 and 100,000 because those numbers satisfy both tests Is this perhaps what you want =SUMPRODUCT(--('Query-Cost Centers'!A26:A116<1000000),--('Query-Cost Centers'!A26:A116899999),'Query-Cost Centers'!C26:C116) -- HTH RP (remove nothere from the email address if mailing direct) "teebee0831" wrote in message ... =SUMIF('Query-Cost Centers'!A26:A116,"<1000000",'Query-Cost Centers'!C26:C116)+SUMIF('Query-Cost Centers'!A26:A116,"899999",'Query-Cost Centers'!C26:C116) I get a result of 0 which is wrong. "Bob Phillips" wrote: SUMIF referring to another sheet is no problem. Maybe the nested SUMIF is causing a problem. Show the data and the formula. -- HTH RP (remove nothere from the email address if mailing direct) "TeeBee0831" wrote in message ... I want to enter a formula on a different worksheet that contains a SUMIF function for values on another sheet. The formula has a nested SUMIF that works fine on the same sheet, but when I try to enter the formula on a different sheet, the results are wrong or I get an error. Can't this be done? |
Sorry, I did fix this but the formula still doesn't work. I get an REF# error.
"teebee0831" wrote: Wrap around? "Bob Phillips" wrote: Did you fix the wrap-around. -- HTH RP (remove nothere from the email address if mailing direct) "teebee0831" wrote in message ... Copying this formula gave me a REF error. "Bob Phillips" wrote: I just created some data and tested that formula and it works for me. It does seem odd though, as it double-counts the items between 899,999 and 100,000 because those numbers satisfy both tests Is this perhaps what you want =SUMPRODUCT(--('Query-Cost Centers'!A26:A116<1000000),--('Query-Cost Centers'!A26:A116899999),'Query-Cost Centers'!C26:C116) -- HTH RP (remove nothere from the email address if mailing direct) "teebee0831" wrote in message ... =SUMIF('Query-Cost Centers'!A26:A116,"<1000000",'Query-Cost Centers'!C26:C116)+SUMIF('Query-Cost Centers'!A26:A116,"899999",'Query-Cost Centers'!C26:C116) I get a result of 0 which is wrong. "Bob Phillips" wrote: SUMIF referring to another sheet is no problem. Maybe the nested SUMIF is causing a problem. Show the data and the formula. -- HTH RP (remove nothere from the email address if mailing direct) "TeeBee0831" wrote in message ... I want to enter a formula on a different worksheet that contains a SUMIF function for values on another sheet. The formula has a nested SUMIF that works fine on the same sheet, but when I try to enter the formula on a different sheet, the results are wrong or I get an error. Can't this be done? |
I just put 999999 in A26, and 3 in C26 and got a value of 3 returned.
I could force a #REF if I took one of the spaces out of the sheet name, which might be what happened to you in correcting the NG wrap-around. -- HTH RP (remove nothere from the email address if mailing direct) "teebee0831" wrote in message ... Sorry, I did fix this but the formula still doesn't work. I get an REF# error. "teebee0831" wrote: Wrap around? "Bob Phillips" wrote: Did you fix the wrap-around. -- HTH RP (remove nothere from the email address if mailing direct) "teebee0831" wrote in message ... Copying this formula gave me a REF error. "Bob Phillips" wrote: I just created some data and tested that formula and it works for me. It does seem odd though, as it double-counts the items between 899,999 and 100,000 because those numbers satisfy both tests Is this perhaps what you want =SUMPRODUCT(--('Query-Cost Centers'!A26:A116<1000000),--('Query-Cost Centers'!A26:A116899999),'Query-Cost Centers'!C26:C116) -- HTH RP (remove nothere from the email address if mailing direct) "teebee0831" wrote in message ... =SUMIF('Query-Cost Centers'!A26:A116,"<1000000",'Query-Cost Centers'!C26:C116)+SUMIF('Query-Cost Centers'!A26:A116,"899999",'Query-Cost Centers'!C26:C116) I get a result of 0 which is wrong. "Bob Phillips" wrote: SUMIF referring to another sheet is no problem. Maybe the nested SUMIF is causing a problem. Show the data and the formula. -- HTH RP (remove nothere from the email address if mailing direct) "TeeBee0831" wrote in message ... I want to enter a formula on a different worksheet that contains a SUMIF function for values on another sheet. The formula has a nested SUMIF that works fine on the same sheet, but when I try to enter the formula on a different sheet, the results are wrong or I get an error. Can't this be done? |
Thanks for the help, but nothing seems to work.
"Bob Phillips" wrote: I just put 999999 in A26, and 3 in C26 and got a value of 3 returned. I could force a #REF if I took one of the spaces out of the sheet name, which might be what happened to you in correcting the NG wrap-around. -- HTH RP (remove nothere from the email address if mailing direct) "teebee0831" wrote in message ... Sorry, I did fix this but the formula still doesn't work. I get an REF# error. "teebee0831" wrote: Wrap around? "Bob Phillips" wrote: Did you fix the wrap-around. -- HTH RP (remove nothere from the email address if mailing direct) "teebee0831" wrote in message ... Copying this formula gave me a REF error. "Bob Phillips" wrote: I just created some data and tested that formula and it works for me. It does seem odd though, as it double-counts the items between 899,999 and 100,000 because those numbers satisfy both tests Is this perhaps what you want =SUMPRODUCT(--('Query-Cost Centers'!A26:A116<1000000),--('Query-Cost Centers'!A26:A116899999),'Query-Cost Centers'!C26:C116) -- HTH RP (remove nothere from the email address if mailing direct) "teebee0831" wrote in message ... =SUMIF('Query-Cost Centers'!A26:A116,"<1000000",'Query-Cost Centers'!C26:C116)+SUMIF('Query-Cost Centers'!A26:A116,"899999",'Query-Cost Centers'!C26:C116) I get a result of 0 which is wrong. "Bob Phillips" wrote: SUMIF referring to another sheet is no problem. Maybe the nested SUMIF is causing a problem. Show the data and the formula. -- HTH RP (remove nothere from the email address if mailing direct) "TeeBee0831" wrote in message ... I want to enter a formula on a different worksheet that contains a SUMIF function for values on another sheet. The formula has a nested SUMIF that works fine on the same sheet, but when I try to enter the formula on a different sheet, the results are wrong or I get an error. Can't this be done? |
Final try. Send me the workbook?
bob dot phillips at tiscali dot co dot uk do the obvious. -- HTH RP (remove nothere from the email address if mailing direct) "teebee0831" wrote in message ... Thanks for the help, but nothing seems to work. "Bob Phillips" wrote: I just put 999999 in A26, and 3 in C26 and got a value of 3 returned. I could force a #REF if I took one of the spaces out of the sheet name, which might be what happened to you in correcting the NG wrap-around. -- HTH RP (remove nothere from the email address if mailing direct) "teebee0831" wrote in message ... Sorry, I did fix this but the formula still doesn't work. I get an REF# error. "teebee0831" wrote: Wrap around? "Bob Phillips" wrote: Did you fix the wrap-around. -- HTH RP (remove nothere from the email address if mailing direct) "teebee0831" wrote in message ... Copying this formula gave me a REF error. "Bob Phillips" wrote: I just created some data and tested that formula and it works for me. It does seem odd though, as it double-counts the items between 899,999 and 100,000 because those numbers satisfy both tests Is this perhaps what you want =SUMPRODUCT(--('Query-Cost Centers'!A26:A116<1000000),--('Query-Cost Centers'!A26:A116899999),'Query-Cost Centers'!C26:C116) -- HTH RP (remove nothere from the email address if mailing direct) "teebee0831" wrote in message ... =SUMIF('Query-Cost Centers'!A26:A116,"<1000000",'Query-Cost Centers'!C26:C116)+SUMIF('Query-Cost Centers'!A26:A116,"899999",'Query-Cost Centers'!C26:C116) I get a result of 0 which is wrong. "Bob Phillips" wrote: SUMIF referring to another sheet is no problem. Maybe the nested SUMIF is causing a problem. Show the data and the formula. -- HTH RP (remove nothere from the email address if mailing direct) "TeeBee0831" wrote in message ... I want to enter a formula on a different worksheet that contains a SUMIF function for values on another sheet. The formula has a nested SUMIF that works fine on the same sheet, but when I try to enter the formula on a different sheet, the results are wrong or I get an error. Can't this be done? |
| All times are GMT +1. The time now is 09:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com