Please help, I'm not sure what function combo to use to get the result I need.

%3CLINGO-SUB%20id%3D%22lingo-sub-1674444%22%20slang%3D%22en-US%22%3EPlease%20help%2C%20I'm%20not%20sure%20what%20function%20combo%20to%20use%20to%20get%20the%20result%20I%20need.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1674444%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20pull%20data%20from%20one%20sheet%20and%20combine%20the%20answer%20with%20a%20sum%20into%20another.%3C%2FP%3E%3CP%3EI%20have%20been%20making%20a%20ledger%20for%20a%20client.%20It%20has%20a%20'PO%20sheet'%20where%20one%20column%20has%20a%20drop-down%20list%20for%20customer%2Fclient%20names%20and%20other%20columns%20including%20date%20entered%2C%20paid%20status%20(yes%2Cno%2Coverdue)%2C%20date%20paid%2C%20amount%2C%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20certain%20customers%20have%20been%20paid%20as%20freelancers%2C%20I%20want%20this%20data%20to%20be%20transposed%20into%20the%20'Payroll%20sheet'%20which%20is%20split%20monthly%20and%20between%20salaried%20staff%20and%20freelance%20staff.%3C%2FP%3E%3CP%3ESo%2C%20for%20example%20Mr%20A.%20gets%20paid%20on%2015th%20May%20%C2%A31500%20and%20on%2031st%20May%20%C2%A3500%20in%20expenses.%20Therefore%20these%202%20payments%20to%20Mr%20A.%20are%20in%20a%20big%20list%20of%20payments%20on%20the%20'PO%20Sheet'%20and%20have%20been%20paid%20in%20May.%20On%20the%20'Payroll%20Sheet'%20in%20the%20row%20of%20May%20payments%20under%20the%20column%20Mr%20A.%20I%20need%20it%20to%20just%20say%20%C2%A32000.%3CBR%20%2F%3EIs%20this%20possible%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20summary%2C%20I%20think%20we%20are%20looking%20at%20two%20conditions%3A%3CBR%20%2F%3ECustomer%20name%20matches%20on%20all%20in%20PO%20column%20with%20name%20on%20Payroll%20column%3CBR%20%2F%3EDate%20paid%20matches%20within%20the%20month%20in%20PO%20column%20to%20the%20month%20on%20Payroll%20row%3CBR%20%2F%3EThen%20all%20matches%20get%20added%20together%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20managed%20(with%20help!)%20to%20get%20it%20working%20in%20GSheets%20using%20the%20following%20formula%20so%20maybe%20this%20one%20just%20needs%20'tweaking'%20a%20little%3F%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DArrayFormula(SUMIF(TEXT(EOMONTH('PO%20Sheet'!K3%3AK%2C-1)%2B1%2C%22MMMM%22)%26amp%3B'PO%20Sheet'!H3%3AH%26amp%3B'PO%20Sheet'!F3%3AF%2CB22%3AB33%26amp%3BC21%3AE21%26amp%3B%22PAID%22%2C'PO%20Sheet'!G3%3AG))%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EAny%20help%20would%20be%20greatly%20appreciated.%3CBR%20%2F%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1674444%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1674755%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20help%2C%20I'm%20not%20sure%20what%20function%20combo%20to%20use%20to%20get%20the%20result%20I%20need.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1674755%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F796093%22%20target%3D%22_blank%22%3E%40Telvixius%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInsert%20a%20pivot%20table.%3C%2FP%3E%3CP%3ECustomer%20%2F%20Organisation%20in%20columns%20area.%3C%2FP%3E%3CP%3EDate%20Paid%20in%20rows%20area.%20Group%20by%20Month.%3C%2FP%3E%3CP%3ETotal%20Paid%20in%20values%20area.%3C%2FP%3E%3CP%3EAnd%20possibly%20Paid%20Y%2FN%3F%20in%20filters%20area.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I want to pull data from one sheet and combine the answer with a sum into another.

I have been making a ledger for a client. It has a 'PO sheet' where one column has a drop-down list for customer/client names and other columns including date entered, paid status (yes,no,overdue), date paid, amount, etc.

 

When certain customers have been paid as freelancers, I want this data to be transposed into the 'Payroll sheet' which is split monthly and between salaried staff and freelance staff.

So, for example Mr A. gets paid on 15th May £1500 and on 31st May £500 in expenses. Therefore these 2 payments to Mr A. are in a big list of payments on the 'PO Sheet' and have been paid in May. On the 'Payroll Sheet' in the row of May payments under the column Mr A. I need it to just say £2000.
Is this possible?

 

In summary, I think we are looking at two conditions:
Customer name matches on all in PO column with name on Payroll column
Date paid matches within the month in PO column to the month on Payroll row
Then all matches get added together

 

I managed (with help!) to get it working in GSheets using the following formula so maybe this one just needs 'tweaking' a little?

=ArrayFormula(SUMIF(TEXT(EOMONTH('PO Sheet'!K3:K,-1)+1,"MMMM")&'PO Sheet'!H3:H&'PO Sheet'!F3:F,B22:B33&C21:E21&"PAID",'PO Sheet'!G3:G))


Any help would be greatly appreciated.
Thanks.

1 Reply
Highlighted

@Telvixius 

Insert a pivot table.

Customer / Organisation in columns area.

Date Paid in rows area. Group by Month.

Total Paid in values area.

And possibly Paid Y/N? in filters area.