• 595K Members
• 5,648 Online
• 724K Conversations

## Finding Annual Count

Highlighted
Occasional Contributor

# 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 format

 ENTRIES LOGIC x Visits Every Three Years divide x by 3 and return x Visits Every Two Years divide x by 2 and return x Visits Every Year return x as it is x Visits Every Six Months multiply x by 2 and return x Visits Every Three Months multiply x by 4 and return

Highlighted

# Re: Finding Annual Count

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))

Related Conversations
Counting Days
Tim Hunter in SQL Server on
2 Replies
How to count multiple values in a cell
Ugarte335 in Excel on
7 Replies
Count until
MBelshaw in Excel on
1 Replies
Pivot table
gabriellerocha in Excel on
5 Replies