Forum Discussion
Finding Annual Count
In the attached workbook i have to find the annual count for all the entries. I'm not able to create a function in VBA that will take into account all the cases. The annual entries are in the below f...
Is there a specific reason you are looking for VBA? if not , you can do this with a formula.
You can try this formula in B2. If the input extends beyond A6, please uupdate formula accordingly.
=LEFT(A2:A6,FIND(" ",A2:A6))/(VLOOKUP(UPPER(IFERROR(LEFT(RIGHT(A2:A6,LEN(A2:A6)-FIND("Every",A2:A6)-5),FIND(" ",RIGHT(A2:A6,LEN(A2:A6)-FIND("Every",A2:A6)-5))-1),"ONE")),{"ONE",1;"TWO",2;"THREE",3;"FOUR",4;"FIVE",5;"SIX",6;"SEVEN",7;"EIGHT",8;"NINE",9},2,"FALSE")*SWITCH(RIGHT(A2:A6,6),"Months",1/12,1))