SUMIFS Function Is Performing Inconsistently

TrainingExcellence

New Member
Joined
Apr 6, 2017
Messages
6
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):

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.
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?
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Watch MrExcel Video

Forum statistics

Threads
1,102,446
Messages
5,486,942
Members
407,572
Latest member
smcexcel

This Week's Hot Topics

Top