I am trying to calculate collections (payment/charges) but the payments and charges are not on the same row. To circumvent that problem, I tried to create a formula essentially stating "if unique id, Item number and date of purchase match, divide the sum of payments by the sum of charges”, but formula didn’t work.



What is the best way to calculate this?

please post an example worksheet, so we can see what the data looks like

The easiest way is to start with defining your table as Table - so you don't have to bother defining any ranges of data. Let's assume you defined a Table EntryRows, which has columns like EntryID, EntryType, EntryDate, ItemNo, RowSum, Collection.
(I used Entry instead of Payment, as it looks like you'll have in same Table payment rows, and charge rows - whatever the last one are. Let's assume all payments have a Type 1, and all charges a Type 2.)


The formula for Collection will be like:
NB! When the formula is used in Table EntryRows, the formula repeats the same value for every row with same set of conditions! When you want to get a single row as result, you must have a separate table or report, where all conditions are set. Table column references to separate Table used there must there be in format [TableName[ColumnName]].

NB! When you don't use Tables, you have to replace Table references with regular ones, i.e. instead of [TableName[ColumnName]] you have to use SheetName!$Column$StartRow:$Column$EndRow, with Column like A, and StartRow and EndRow like 2 and 10. A Table reference preceeded with @ refers to value in same row.
And be sure all ranges referenced in SUMIF formula are of same dimension!