• 463K Members
• 8,694 Online
• 560K Conversations

## Finding Annual Count

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

# 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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies