Help summarising data based on date

[calof1]

Hi All,

I have a spreadsheet which includes large sets of data, showing an account and the person responsible (managers) for it across many months. We wish to show some reporting on the number of accounts and managers. For each month i need to count the number of accounts/managers which have a value greater than $0.00, and show those which are added.

Whilst my spreadsheet is very large and doing this manually is very time consuming, i am hoping to use a formula to work this out for me. Once an account is new, it will have a positive balance, and have not previously had an account value.

If anyone has any ideas on how to do this, it would be very much appreciated. I have included some sample data and workings.

Many thanks,

[#2]

How you are differentiating between New & Old Managers as well Funds since entries are straight forward Fund 1 to 10 and Manager 1 to 3 !!

[#3 · Accepted solution]

Hi Rajesh,

Thank you kindly for your reply. The same data i used i basic, so i admit makes it hard to illustrate the concept. To assist i will try and explain a bit further, for my data we have each month as a column. When we add new accounts we add a row. So a new account is active when the row goes from $0. 00 to any amount above this (as money have been invested now). So for this month a new fund has been added.

Sometimes we have people who already have accounts with us, but set up another one. So for example if we have a new account have funds invested (goes from 0 to anything above) we have a new account, and if the "Manager" isn't on our list (column B), we count this as a new "Manager". So if we have a new account and manager invest, we count this as a new manager and fund for that month.

Does this help at all?

Many thanks for your time and assistance.

kind regards,

[#4]

@calof1 

Check the attached WB, and the read the instruction also. I do believe that is what you were looking for,, in case of any further issue,, do reply this post,,, and if my suggested method works for you then you may accept it as best answer as well like.

[#5]

Hi Rajesh,

Thanks again for your help, very much appreciated.

I have had a look at the spreadsheet and just have a query regarding the old v new. Given my spreadsheet reports across a range of months, i would need a formula to determine the old v new. For example if a fund or manager is new in April, then it isn't new in the following months. Just seeing if we capture this. For logic of a new fund i think if the previous month is $0.00 then the next month is greater than $0.00 we can count it as new.

Then to show if a manager is new, the logic would be if the fund is new, and the "Manager" named in the row had no other accounts previously (eg any rows in the previous months with a balance above $0.). I believe this will show the new funds/managers in their respective months as they come in. Do you have any ideas on how i can implement this logic to help with showing the new accounts/managers?

Once again greatly appreciate your thoughts and assistance with this, and can discuss any details further. I have liked your comments and will show each like and best response as requested for you.

Many thanks,

[#6]

Glad to help you,,, plzz accept my post as best Answer since it'w working ,, keep asking ☺

[#7]

Hi Rajesh,

i have accepted as best response for you. Many thanks.

Just wondering if you have any ideas on how i can input the below logic

When we add new accounts we add a row. So a new account is active when the row goes from $0. 00 to any amount above this (as money have been invested now). So for this month a new fund has been added.

Sometimes we have people who already have accounts with us, but set up another one. So for example if we have a new account have funds invested (goes from 0 to anything above) we have a new account, and if the "Manager" isn't on our list (column B), we count this as a new "Manager". So if we have a new account and manager invest, we count this as a new manager and fund for that month.

Many thanks