Summing on Date Ranges across the top and multiple rows

user_18274550 0
Viewed 21

So I have I guess what you would call a "crosstab" file as an income statement. It has Month end dates as column headers and different criteria (account grouping names, which can be repeated, hence why xlookup won't work here) as the row names. So one thing I am being tasked with doing is summing by quarter. If "Labor Category X" wasn't repeated on multiple rows (salary, tax, vacation, for Labor Cat X rolls up into this category for ex) sum(xlookup) across the last 3 months would work. But since there are multiple rows where there is a match, xlookup fails. I know I need some combo of sumproduct and index but can't quite wrap my finger around it.

Picture is worth a thousand words here:

enter image description here

excel-formula
1 Answers

The general problem you are describing is matching with multiple criteria, i.e. across multiple columns.

The specific problem you described as an example, where you want to calculate the sum for a department by quarter, however, does not require looking at the "accounts" column (tax, salary, ...)

A suitable solution for this specific problem would be the following formula:

=SUM((department_range=department)*(date_range<=EOMONTH(end_of_quarter,0))*(date_range>=DATE(YEAR(start_of_quarter),MONTH(start_of_quarter)-2,1))*($data_range))

illustrated with actual cell references in the following screenshot of the example:

Exmaple of Quarter Sums Note that the 'quarters' are formatted as date values here. E.g. 'Mar 2022' is in fact 3/1/2021 (US date format)

If there are more problems that you are looking to resolve, please clarify those with a few more details and an example of the desired outcome.

user_171587030
Related