So I have a giant list of data I am looking to turn into a report. I am using a formula (which I havent totally figured out yet...) on a seperate sheet from my data(My_Table)<where I put all my raw data. I have most of the formula done {=SUMPRODUCT(INDEX(My_Table,0,17),((ISNUMBER(LEFT(SUBSTITUTE(INDEX(My_Table,0,7),"-",""),6))+0)+(ISNUMBER(INDEX(My_Table,0,7))+0)+(LEN(INDEX(My_Table,0,7))=16)+(LEN(INDEX(My_Table,0,7))=15)+(LEN(INDEX(My_Table,0,7))=8))+(LEN(INDEX(My_Table,0,7))=7),--(INDEX(My_Table,0,2)="SMITH, JOHN"),--(INDEX(My_Table,0,10)="MACH"),--(INDEX(My_Table,0,7)<>(SEARCH("BU",(INDEX(My_Table,0,7)),0))))} but need to fix the last part and add the month criteria. It needs to exclude numbers from column 7 which have BU in it( for example I22BU2E) and not add the numbers in column 17 if they are found. Also I need to only include numbers to be added which lie in the month of January for example. My cells are in the date format ##/##/#### and I tried using --MONTH(INDEX(My_Table,0,6))=1 and am getting 0... I cannot upload the file but could always provide sample workbooks to assist if there is more than a simple tweak necessary.
Thanks!
EDIT:There are empty cells and a cell which says Date in column 6
Bookmarks