Good morning!
I need a formula to be able to count periods of activity.
Using the table below as an example, activity is denoted by the letter 'M'. The 'M's against Jan, Feb and Mar would be classed as 1 single period of activity. Across the whole year there are 3 periods of activity.
Activity Formula.jpg
Does anyone know of a formula that would be able to calculate this please?
This Macro would do it for your example
Please Login or Register to view this content.
Please do add reputation where you see fit, it's nice to be nice and we all enjoy a pat on the back
Please also mark your thread as solved once it has been.
That's excellent, thank you and works nicely. However.....
I've since been sent the actual spreadsheet that the Macro needs to be applied to. It contains 810 rows and each row needs to be tallied individually. In addition, the rows contain EITHER 'M' or 'R'.
Can the Macro be modified to take the extra parameters into account?
Thanks in advance.
Please try at M3
=SUM(N(FREQUENCY(IF(A2:L2<>0,COLUMN(A2:L2)),IF(A2:L2="",COLUMN(A2:L2)))>0))
Press Ctrl+Shift+Enter and drag down
That is perfect, thank you!!!
Last edited by AliGW; 12-30-2019 at 07:38 AM. Reason: Please don't quote unnecessarily!
------SOLVED-----
Good morning everyone.
I'm looking for a formula which is a variant of the answer to my original question please.
This time, using the example in my first post, I need the formula to calculate how many times there are 2 consecutive M's - so the answer I'd be looking for is 1.
Any help will be greatly appreciated.
----SOLVED----
Just tweaked the original formula to {=SUM(N(FREQUENCY(IF(A1:G1<>0,COLUMN(A1:G1)),IF(A1:G1="",COLUMN(A1:G1)))=2))}
Last edited by andyporter1975; 12-30-2019 at 07:47 AM. Reason: Solved
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks