#1
|
|||
|
|||
Summing various rows of criteria match
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? |
#2
|
|||
|
|||
Quote:
|
#3
|
|||
|
|||
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: Code:
=SUMIFS([RowSum],[EntryID],[@EntryID],[EntryDate], [@EntryDate], [ItemNo], [@ItemNo],[EntryType],1)/SUMIFS([RowSum],[EntryID],[@EntryID],[EntryDate], [@EntryDate], [ItemNo], [@ItemNo],[EntryType],2) 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! |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Add criteria to MAX INDEX MATCH | ollyhughes | Excel | 2 | 02-25-2022 03:38 AM |
Formula that looks at more than one criteria and then adds up those that match | DazzyBee | Excel | 2 | 01-20-2017 11:29 PM |
Create a unique list of values that match a criteria, sorted in order of another criteria | BradRichardson | Excel | 2 | 01-03-2017 12:25 AM |
Using a VLOOKUP - when 2 rows match criteria it returns the first value in the cel only how 2 change | djrobst | Excel | 4 | 10-28-2015 01:32 AM |
Count unique values that match 2 or more criteria | caeiro01 | Excel | 1 | 10-25-2015 02:34 AM |