|
Sumifs
Hi,
I link sumifs function to another worksheet and whenever I recalculate the sheet with the sumifs functions with the linked pages closed, I get the #VALUE! error. Any idea how to avoid this error and have the function either pull the data or keep what ever answers I had in the cell before it was updated? Thank you, |
Sumifs
SUMIF/SUMIFS don't work on closed files. Use SUMPRODUCT instead.
-- Biff Microsoft Excel MVP "David" wrote in message ... Hi, I link sumifs function to another worksheet and whenever I recalculate the sheet with the sumifs functions with the linked pages closed, I get the #VALUE! error. Any idea how to avoid this error and have the function either pull the data or keep what ever answers I had in the cell before it was updated? Thank you, |
Sumifs
I see where you're going with the sumproduct, but that's not exactly what i
need. I'm trying to match "T. Valko" wrote: SUMIF/SUMIFS don't work on closed files. Use SUMPRODUCT instead. -- Biff Microsoft Excel MVP "David" wrote in message ... Hi, I link sumifs function to another worksheet and whenever I recalculate the sheet with the sumifs functions with the linked pages closed, I get the #VALUE! error. Any idea how to avoid this error and have the function either pull the data or keep what ever answers I had in the cell before it was updated? Thank you, |
Sumifs
Whatever you use SUMIF/SUMIFS to do you can use SUMPRODUCT to do.
-- Biff Microsoft Excel MVP "David" wrote in message ... I see where you're going with the sumproduct, but that's not exactly what i need. I'm trying to match "T. Valko" wrote: SUMIF/SUMIFS don't work on closed files. Use SUMPRODUCT instead. -- Biff Microsoft Excel MVP "David" wrote in message ... Hi, I link sumifs function to another worksheet and whenever I recalculate the sheet with the sumifs functions with the linked pages closed, I get the #VALUE! error. Any idea how to avoid this error and have the function either pull the data or keep what ever answers I had in the cell before it was updated? Thank you, |
Sumifs
Sorry about that, i pressed post too early. What i'm trying to do is match a
specfic store in my company with a specific account and there are multiple accounts in this list for each store. "David" wrote: I see where you're going with the sumproduct, but that's not exactly what i need. I'm trying to match "T. Valko" wrote: SUMIF/SUMIFS don't work on closed files. Use SUMPRODUCT instead. -- Biff Microsoft Excel MVP "David" wrote in message ... Hi, I link sumifs function to another worksheet and whenever I recalculate the sheet with the sumifs functions with the linked pages closed, I get the #VALUE! error. Any idea how to avoid this error and have the function either pull the data or keep what ever answers I had in the cell before it was updated? Thank you, |
Sumifs
Post the *exact* SUMIF/SUMIFS formula you are trying to use.
-- Biff Microsoft Excel MVP "David" wrote in message ... Sorry about that, i pressed post too early. What i'm trying to do is match a specfic store in my company with a specific account and there are multiple accounts in this list for each store. "David" wrote: I see where you're going with the sumproduct, but that's not exactly what i need. I'm trying to match "T. Valko" wrote: SUMIF/SUMIFS don't work on closed files. Use SUMPRODUCT instead. -- Biff Microsoft Excel MVP "David" wrote in message ... Hi, I link sumifs function to another worksheet and whenever I recalculate the sheet with the sumifs functions with the linked pages closed, I get the #VALUE! error. Any idea how to avoid this error and have the function either pull the data or keep what ever answers I had in the cell before it was updated? Thank you, |
Sumifs
This is my current sumifs formula:
=SUMIFS('[4-204 1208.xls]JE DATA'!$E:$E,'[4-204 1208.xls]JE DATA'!$B:$B,A5,'[4-204 1208.xls]JE DATA'!$C:$C,$F$2) It currently works as is, but i'd like to see try this Sumproduct function sounds like a better solution. Thanks, David "T. Valko" wrote: Whatever you use SUMIF/SUMIFS to do you can use SUMPRODUCT to do. -- Biff Microsoft Excel MVP "David" wrote in message ... I see where you're going with the sumproduct, but that's not exactly what i need. I'm trying to match "T. Valko" wrote: SUMIF/SUMIFS don't work on closed files. Use SUMPRODUCT instead. -- Biff Microsoft Excel MVP "David" wrote in message ... Hi, I link sumifs function to another worksheet and whenever I recalculate the sheet with the sumifs functions with the linked pages closed, I get the #VALUE! error. Any idea how to avoid this error and have the function either pull the data or keep what ever answers I had in the cell before it was updated? Thank you, |
Sumifs
The SUMPRODUCT version would be:
=SUMPRODUCT(--('[4-204 1208.xls]JE DATA'!$B:$B=A5),--('[4-204 1208.xls]JE DATA'!$C:$C=$F$2),'[4-204 1208.xls]JE DATA'!$E:$E) Although you can use entire columns as range references with SUMPRODUCT in Excel 2007 (only) I still wouldn't do it if you're not using all 1,000,000+ rows for data. I'd use the smallest specific range needed. -- Biff Microsoft Excel MVP "David" wrote in message ... This is my current sumifs formula: =SUMIFS('[4-204 1208.xls]JE DATA'!$E:$E,'[4-204 1208.xls]JE DATA'!$B:$B,A5,'[4-204 1208.xls]JE DATA'!$C:$C,$F$2) It currently works as is, but i'd like to see try this Sumproduct function sounds like a better solution. Thanks, David "T. Valko" wrote: Whatever you use SUMIF/SUMIFS to do you can use SUMPRODUCT to do. -- Biff Microsoft Excel MVP "David" wrote in message ... I see where you're going with the sumproduct, but that's not exactly what i need. I'm trying to match "T. Valko" wrote: SUMIF/SUMIFS don't work on closed files. Use SUMPRODUCT instead. -- Biff Microsoft Excel MVP "David" wrote in message ... Hi, I link sumifs function to another worksheet and whenever I recalculate the sheet with the sumifs functions with the linked pages closed, I get the #VALUE! error. Any idea how to avoid this error and have the function either pull the data or keep what ever answers I had in the cell before it was updated? Thank you, |
Sumifs
That worked great! Thanks!
"T. Valko" wrote: The SUMPRODUCT version would be: =SUMPRODUCT(--('[4-204 1208.xls]JE DATA'!$B:$B=A5),--('[4-204 1208.xls]JE DATA'!$C:$C=$F$2),'[4-204 1208.xls]JE DATA'!$E:$E) Although you can use entire columns as range references with SUMPRODUCT in Excel 2007 (only) I still wouldn't do it if you're not using all 1,000,000+ rows for data. I'd use the smallest specific range needed. -- Biff Microsoft Excel MVP "David" wrote in message ... This is my current sumifs formula: =SUMIFS('[4-204 1208.xls]JE DATA'!$E:$E,'[4-204 1208.xls]JE DATA'!$B:$B,A5,'[4-204 1208.xls]JE DATA'!$C:$C,$F$2) It currently works as is, but i'd like to see try this Sumproduct function sounds like a better solution. Thanks, David "T. Valko" wrote: Whatever you use SUMIF/SUMIFS to do you can use SUMPRODUCT to do. -- Biff Microsoft Excel MVP "David" wrote in message ... I see where you're going with the sumproduct, but that's not exactly what i need. I'm trying to match "T. Valko" wrote: SUMIF/SUMIFS don't work on closed files. Use SUMPRODUCT instead. -- Biff Microsoft Excel MVP "David" wrote in message ... Hi, I link sumifs function to another worksheet and whenever I recalculate the sheet with the sumifs functions with the linked pages closed, I get the #VALUE! error. Any idea how to avoid this error and have the function either pull the data or keep what ever answers I had in the cell before it was updated? Thank you, |
Sumifs
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "David" wrote in message ... That worked great! Thanks! "T. Valko" wrote: The SUMPRODUCT version would be: =SUMPRODUCT(--('[4-204 1208.xls]JE DATA'!$B:$B=A5),--('[4-204 1208.xls]JE DATA'!$C:$C=$F$2),'[4-204 1208.xls]JE DATA'!$E:$E) Although you can use entire columns as range references with SUMPRODUCT in Excel 2007 (only) I still wouldn't do it if you're not using all 1,000,000+ rows for data. I'd use the smallest specific range needed. -- Biff Microsoft Excel MVP "David" wrote in message ... This is my current sumifs formula: =SUMIFS('[4-204 1208.xls]JE DATA'!$E:$E,'[4-204 1208.xls]JE DATA'!$B:$B,A5,'[4-204 1208.xls]JE DATA'!$C:$C,$F$2) It currently works as is, but i'd like to see try this Sumproduct function sounds like a better solution. Thanks, David "T. Valko" wrote: Whatever you use SUMIF/SUMIFS to do you can use SUMPRODUCT to do. -- Biff Microsoft Excel MVP "David" wrote in message ... I see where you're going with the sumproduct, but that's not exactly what i need. I'm trying to match "T. Valko" wrote: SUMIF/SUMIFS don't work on closed files. Use SUMPRODUCT instead. -- Biff Microsoft Excel MVP "David" wrote in message ... Hi, I link sumifs function to another worksheet and whenever I recalculate the sheet with the sumifs functions with the linked pages closed, I get the #VALUE! error. Any idea how to avoid this error and have the function either pull the data or keep what ever answers I had in the cell before it was updated? Thank you, |
| All times are GMT +1. The time now is 12:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com