TrainingExcellence
New Member
- Joined
- Apr 6, 2017
- Messages
- 10
- Office Version
- 365
This problem has been driving me nuts, because it should be simple, but it's not working. I have the following formula in a table called tblCurrentHoldings on the sheet CurrentHoldings (see screenshot: https://1drv.ms/u/s!ArArDJ7WmD62grkdhF-Y-3Epey_0_g?e=vkjFxq):
The formula is supposed to sum the values of a column in a table called tblHoldings on the sheet Retirement Holdings that match the investment type and date range listed in tblCurrentHoldings. The function returns "0" even though there are qualifying values to be summed. When I evaluated the function, the date references in tblCurrentHoldings returned the #VALUE error.
See screenshot: https://1drv.ms/u/s!ArArDJ7WmD62grkeayVPGJnM-qHSqg?e=JYXNSm.
What is really weird is when, during my troubleshooting, I copied tblCurrentHoldings onto the Retirement Holdings sheet next to tblHoldings, the formula worked! See screenshot: https://1drv.ms/u/s!ArArDJ7WmD62grkcsKH0V9fzmP7YdQ?e=Rpfyp8.
Why would the SUMIFS function work on one worksheet, but not on another. To my knowledge, I am using structured table references correctly. I tried adding "tblCurrentHoldings" before the "[@Dates]" reference to make sure there was no mistaking the table, but Excel drops the table reference when I press Enter.
Any idea what is wrong?
Code:
=SUMIFS(tblHoldings[Amount],tblHoldings[Dates],">="&[@Dates],tblHoldings[Dates],"<"&CurrentHoldings!C4,tblHoldings[Investment],tblCurrentHoldings[[#Headers],[LargeStock]])The formula is supposed to sum the values of a column in a table called tblHoldings on the sheet Retirement Holdings that match the investment type and date range listed in tblCurrentHoldings. The function returns "0" even though there are qualifying values to be summed. When I evaluated the function, the date references in tblCurrentHoldings returned the #VALUE error.
What is really weird is when, during my troubleshooting, I copied tblCurrentHoldings onto the Retirement Holdings sheet next to tblHoldings, the formula worked! See screenshot: https://1drv.ms/u/s!ArArDJ7WmD62grkcsKH0V9fzmP7YdQ?e=Rpfyp8.
Why would the SUMIFS function work on one worksheet, but not on another. To my knowledge, I am using structured table references correctly. I tried adding "tblCurrentHoldings" before the "[@Dates]" reference to make sure there was no mistaking the table, but Excel drops the table reference when I press Enter.
Any idea what is wrong?
