I manage a sales team, and I keep track of their data on Excel.
What I'd like to know if there is any function to write that would count the numbers in another cell.
For example, Column E is tracking "Total Sales" whereas Column L is tracking "Total $ Amount of Sales" which I manually enter. So my question is if Column L is entered as " =24+30+60+60" is there any formula to write that would give me "4" in Column E.
Not with an inbuilt function, but you can add a user-defined-function to your workbook to do that.
Function COUNTP(Ref As Range) As Long 'Counts the number of items in a cell's formula 'as determined by + and - operators COUNTP = Len(Ref.Cells(1, 1).Formula) - _ Len(Replace(Replace(Ref.Cells(1, 1).Formula, "+", ""), "-", "")) + 1 End Function
How to install the User Defined Function:
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook
The function is installed and ready to use.
If your formula were in cell A1, then in another cell enter this to get the count of items in that formula:
=COUNTP(A1)
Last edited by JBeaucaire; 10-04-2011 at 10:26 AM. Reason: Corrected sample formula at end of post
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Will return the result of 1. I take it the P is silent, as in 'swimming pool'Originally Posted by JBeaucaire
Sounds dynamite. I'll give it a try today, and if this works, would save me a lot of time!
Last edited by shg; 10-05-2011 at 11:13 AM. Reason: deleted quote
Great. Oh, and please do not QUOTE my entire posts into yours. Just use the Quick Reply button. Much easier to read threads.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks