| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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? |
|
#2
|
|||
|
|||
|
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? |
|
#3
|
|||
|
|||
|
=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? |
|
#4
|
|||
|
|||
|
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? |
|
#5
|
|||
|
|||
|
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? |
|
#6
|
|||
|
|||
|
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? |
|
#7
|
|||
|
|||
|
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? |
|
#8
|
|||
|
|||
|
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? |
|
#9
|
|||
|
|||
|
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? |
|
#10
|
|||
|
|||
|
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? |
|
#11
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
| Function to list values of last 3 non-blank cells in a vertical bl | Excel Worksheet Functions | |||
| SUMIF function | Excel Worksheet Functions | |||
| SUM(IF( Array to avoid #NUM! values | Excel Worksheet Functions | |||
| need to save values from a function before it changes | Excel Worksheet Functions | |||
Linear Mode
