Funding Tracking

%3CLINGO-SUB%20id%3D%22lingo-sub-2575421%22%20slang%3D%22en-US%22%3EFunding%20Tracking%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2575421%22%20slang%3D%22en-US%22%3EI%20need%20to%20figure%20out%20the%20easiest%20way%20to%20track%20the%20source%20of%20funding%20for%20payments.%20For%20example%20I%20have%20a%20list%20of%20500%20payments%20ranging%20from%20%241%20to%20%245M.%3CBR%20%2F%3E%3CBR%20%2F%3EDepending%20on%20the%20type%20of%20invoice%2C%20and%20the%20amount%20of%20the%20payment%2C%20the%20payments%20come%20from%20different%20sources.%3CBR%20%2F%3E%3CBR%20%2F%3EExample%3A%3CBR%20%2F%3EType%20A%20Invoice%3A%20first%20%241%2C000%20paid%20from%20account%201%2C%20anything%20over%20%241%2C000%20paid%20from%20account%202.%3CBR%20%2F%3E%3CBR%20%2F%3EType%20B%20Invoice%3A%20first%20%24100%2C000%20paid%20from%20account%201%2C%20anything%20over%20paid%20from%20account%202.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20want%20to%20calculate%20how%20much%20was%20paid%20from%20account%201%20and%20account%202%20for%20each%20type%20of%20invoice.%20Also%2C%20if%20possible%2C%20can%20I%20also%20calculate%20the%20amounts%20per%20fiscal%20years%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20tried%20adding%20to%20conditional%20formulas%20into%20a%20pivot%20table.%20(Couldn%E2%80%99t%20figure%20out%20how)%20Any%20ideas%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2575421%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2575488%22%20slang%3D%22en-US%22%3ERe%3A%20Funding%20Tracking%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2575488%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1110138%22%20target%3D%22_blank%22%3E%40Jtennile%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20that%20could%20be%20PivotTable%2C%20depends%20on%20how%20your%20data%20is%20structured.%3C%2FP%3E%0A%3CP%3EConditional%20formatting%20-%20see%20for%20example%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fpivot-table%2Fpivot-table-conditional-formatting%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EPivot%20Table%3A%20Pivot%20table%20conditional%20formatting%20%7C%20Exceljet%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor
I need to figure out the easiest way to track the source of funding for payments. For example I have a list of 500 payments ranging from $1 to $5M.

Depending on the type of invoice, and the amount of the payment, the payments come from different sources.

Example:
Type A Invoice: first $1,000 paid from account 1, anything over $1,000 paid from account 2.

Type B Invoice: first $100,000 paid from account 1, anything over paid from account 2.

I want to calculate how much was paid from account 1 and account 2 for each type of invoice. Also, if possible, can I also calculate the amounts per fiscal years?

I tried adding to conditional formulas into a pivot table. (Couldn’t figure out how) Any ideas?
2 Replies

@Jtennile 

Perhaps that could be PivotTable, depends on how your data is structured.

Conditional formatting - see for example here Pivot Table: Pivot table conditional formatting | Exceljet

@Jtennile

 

It would be easier to come up with a solution without having to re-create the problem ourselves.  If you provide a sample dataset maybe someone who thinks this puzzle is worth the challenge.

 

cheers