I have a formula that is looking at a worksheet that is supposed to find all dates that include the "mmm" format. All of them work except for Jan. What it is doing is counting every month exactly as is but the range picks up every blank from row 2 to 100 as January. I am not sure what I have done wrong.
=SUMPRODUCT(--(TEXT(Expense!$A$2:$A$100,"mmm")=(TEXT(K2,"mmm"))))
******** ******************** ************************************************************************><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=5><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left>
Microsoft Excel - OPPM Convocation.xlsm</TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: 14.0 : OS = </TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=5><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(
F)ile (
E)dit (
V)iew (
I)nsert (
O)ptions (
T)ools (
D)ata (
W)indow (
H)elp
(A)bout</TD><TD vAlign=center align=right><FORM name=formCb755237><INPUT type=button value="Copy Formula" name=btCb873980 *******='window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);'></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=5><TABLE border=0><TBODY><TR><FORM name=formFb078704><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value" name=sltNb935705><OPTION value==EOMONTH(Start_Date,-1)+1 selected>I2<OPTION value==EOMONTH(I2,0)+1>J2<OPTION value==EOMONTH(J2,0)+1>K2<OPTION value==EOMONTH(K2,0)+1>L2<OPTION value='=SUMPRODUCT(--(TEXT(Expense!$A$2:$A$100,"mmm")=(TEXT(I2,"mmm"))))'>I3<OPTION value='=SUMPRODUCT(--(TEXT(Expense!$A$2:$A$100,"mmm")=(TEXT(J2,"mmm"))))'>J3<OPTION value='=SUMPRODUCT(--(TEXT(Expense!$A$2:$A$100,"mmm")=(TEXT(K2,"mmm"))))'>K3<OPTION value='=SUMPRODUCT(--(TEXT(Expense!$A$2:$A$10,"mmm")=(TEXT(L2,"mmm"))))'>L3</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>
=</TD><TD align=left bgColor=white><INPUT size=80 value==EOMONTH(Start_Date,-1)+1 name=txbFb426622></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%">
</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>I</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>J</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>K</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>L</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 11pt; VERTICAL-ALIGN: middle; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">
Nov</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 11pt; VERTICAL-ALIGN: middle; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">
Dec</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 11pt; VERTICAL-ALIGN: middle; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">
Jan</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 11pt; VERTICAL-ALIGN: middle; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">
Feb</TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>3</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 11pt; VERTICAL-ALIGN: middle; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">
1</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 11pt; VERTICAL-ALIGN: middle; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">
1</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 11pt; VERTICAL-ALIGN: middle; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">
90</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 11pt; VERTICAL-ALIGN: middle; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">
1</TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=5><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left>
Main</TD><TD> </TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</CENTER>
