Help to identify which money new accounts or managers are added

%3CLINGO-SUB%20id%3D%22lingo-sub-2429164%22%20slang%3D%22en-US%22%3EHelp%20to%20identify%20which%20money%20new%20accounts%20or%20managers%20are%20added%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2429164%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20which%20includes%20large%20sets%20of%20data%2C%20showing%20an%20account%20and%20the%20person%20responsible%20(managers)%20for%20it%20across%20many%20months.%20We%20wish%20to%20show%20some%20reporting%20on%20the%20number%20of%20accounts%20and%20managers.%20For%20each%20month%20i%20need%20to%20count%20the%20number%20of%20accounts%2Fmanagers%20which%20have%20a%20value%20greater%20than%20%240.00%2C%20and%20show%20those%20which%20are%20added.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhilst%20my%20spreadsheet%20is%20very%20large%20and%20doing%20this%20manually%20is%20very%20time%20consuming%2C%20i%20am%20hoping%20to%20use%20a%20formula%20to%20work%20this%20out%20for%20me.%20Once%20an%20account%20is%20new%2C%20it%20will%20have%20a%20positive%20balance%2C%20and%20have%20not%20previously%20had%20an%20account%20value.%20The%20logic%20to%20be%20able%20to%20determine%20this%20would%20be%20as%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EWhen%20we%20add%20new%20accounts%20we%20add%20a%20row.%20So%20a%20new%20account%20is%20active%20when%20the%20row%20goes%20from%20%240.%2000%20to%20any%20amount%20above%20this%20(as%20money%20have%20been%20invested%20now).%20So%20for%20this%20month%20a%20new%20fund%20has%20been%20added.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3ESometimes%20we%20have%20people%20who%20already%20have%20accounts%20with%20us%2C%20but%20set%20up%20another%20one.%20So%20for%20example%20if%20we%20have%20a%20new%20account%20have%20funds%20invested%20(goes%20from%200%20to%20anything%20above)%20we%20have%20a%20new%20account%2C%20and%20if%20the%20%22Manager%22%20isn't%20on%20our%20list%20(column%20B)%2C%20we%20count%20this%20as%20a%20new%20%22Manager%22.%20So%20if%20we%20have%20a%20new%20account%20and%20manager%20invest%2C%20we%20count%20this%20as%20a%20new%20manager%20and%20fund%20for%20that%20month.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anyone%20has%20any%20ideas%20on%20how%20to%20do%20this%2C%20it%20would%20be%20very%20much%20appreciated.%20I%20have%20included%20some%20sample%20data%20and%20workings.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2429164%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20Scripts%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUser%20Adoption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Regular Contributor

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. The logic to be able to determine this would be as below.

 

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.

 

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,

0 Replies