SUMIFS returns "0" incorrectly. How do I adjust Cell in VBA to return actual sum value?

[u228018]

I made the following formula to automatically update a report to return Day, Week or Monthly values based on Cell B2 (Which changes based on user input from userform)

=SUMIFS(Qty,Custodian,$B10,Defi,"Active Others",Age,C$4,Method,"Electronic",Rec_Rej,$C$3,day,$A$2). The columns have defined names which match the formula above. My issue is with the "day", "week", and "month" changes. Something happens that when it switches from "month" to "day" the formula breaks and simply returns "0" despite the actual sum being a value much higher. I know this has something to do with text-columns, but I am utterly confused on how to correct it. Once I understand how to manually correct it each time, I can easily correct my userform vba. Please help.

this formula is pulling from a data page that looks like this:
[TABLE="width: 1459"]
<TBODY>[TR]
[TD]Custodian 1</SPAN>[/TD]
[TD]Today's Date</SPAN>[/TD]
[TD]Defi Population</SPAN>[/TD]
[TD]Order Age[/TD]
[TD]Method</SPAN>[/TD]
[TD]Reject Population</SPAN>[/TD]
[TD]Qty[/TD]
[TD]Day</SPAN>[/TD]
[TD]Week</SPAN>[/TD]
[TD]Month</SPAN>[/TD]
[/TR]
[TR]
[TD]CustA[/TD]
[TD]10/1/2012</SPAN>[/TD]
[TD]Active Others</SPAN>[/TD]
[TD]6 to 10 days</SPAN>[/TD]
[TD]Rejected</SPAN>[/TD]
[TD]Electronic</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[TD]Monday, 10/1/2012</SPAN>[/TD]
[TD]Week of 9/28 - 10/4 2012</SPAN>[/TD]
[TD]1-Oct-12</SPAN>[/TD]
[/TR]
[TR]
[TD]CustB[/TD]
[TD]10/1/2012</SPAN>[/TD]
[TD]Active Others</SPAN>[/TD]
[TD]6 to 10 days</SPAN>[/TD]
[TD]Not Received[/TD]
[TD]Electronic</SPAN>[/TD]
[TD]9</SPAN>[/TD]
[TD]Tuesday, 10/2/2012</SPAN>[/TD]
[TD]Week of 9/28 - 10/4 2012</SPAN>[/TD]
[TD]1-Oct-12</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL span=2><COL><COL><COL><COL></COLGROUP>[/TABLE]

 

[u228018]

I mistakenly said cell B2 above. The cell that changes each time is reference $A$2 (To either: day: Monday, 10/1/2012, week: Week of 9/28 - 10/4 2012</SPAN>, or month: [TABLE="class: cms_table"]
<TBODY>[TR]
[TD]1-Oct-12. This is the area that is a problem. It's driving me nuts </SPAN>[/TD]
[/TR]
</TBODY>[/TABLE]


</SPAN></SPAN>

 

[u228018]

I figured it out. I was confusing excel by defining my columns as "day" "week" "Month", etc. I fixed this by re-defining them as "day_lkp", "month_lkp", and "week_lkp".
The below formula works now:
=SUMIFS(Qty,Custodian,$B6,Defi,"Active WFDC",Age,C$4,Method,"Electronic",Rec_Rej,$C$3,Month_Lkp,A2)