Sum of unique values with multiple matching criteria

[fjdurbin]

I'm dumping a large volume of data each month into Excel. I need to summarize the data by Budget type and Category and possibly a third variable such as year. In the image below the result I hope for is a formula will calculate the sum of the unique values in column D when BudgetType is equal to Actual and Category is equal to G&A Costs. In this example I would be adding the following four numbers to arrive at 119.0633.

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]0.9[/TD]
[/TR]
[TR]
[TD="align: right"]9.4193[/TD]
[/TR]
[TR]
[TD="align: right"]0.744[/TD]
[/TR]
[TR]
[TD="align: right"]108[/TD]
[/TR]
[TR]
[TD="align: right"]119.0633[/TD]
[/TR]
</tbody>[/TABLE]

The formula in this example would appear in D23. I'm not interested in a Pivot table due to complexity and volume. I played a bit with SumIfS but have not been successful. Any suggestions?...JD

 

[aaalviny]

I'm dumping a large volume of data each month into Excel. I need to summarize the data by Budget type and Category and possibly a third variable such as year. In the image below the result I hope for is a formula will calculate the sum of the unique values in column D when BudgetType is equal to Actual and Category is equal to G&A Costs. In this example I would be adding the following four numbers to arrive at 119.0633.

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]0.9[/TD]
[/TR]
[TR]
[TD="align: right"]9.4193[/TD]
[/TR]
[TR]
[TD="align: right"]0.744[/TD]
[/TR]
[TR]
[TD="align: right"]108[/TD]
[/TR]
[TR]
[TD="align: right"]119.0633[/TD]
[/TR]
</tbody>[/TABLE]

The formula in this example would appear in D23. I'm not interested in a Pivot table due to complexity and volume. I played a bit with SumIfS but have not been successful. Any suggestions?...JD

As a suggestion:

1. the root cause of this problem lies on the quality of data, see if you have any ways to get rid of redundant same data record
2. the summary should be placed on top of the worksheet, instead of bottom. this can create an easily readible worksheet (user can immediately see the odd cells (i.e. summary) at the top, and user won't need to intentionally search for that, and user can be notified of those odd cells immediately without scrolling)

to tackle the problem, the best way is to sort your data, by FY ascending then BudgetType ascending then Category ascending
add a column to check if, for each record, whether all the fields are identical to the record immediately above it. use 1 and 0 to indicate it

finally, sumif or sumproduct works (or better if you can use a new column to accumulate the sum)

 

[fjdurbin]

You said:
1. the root cause of this problem lies on the quality of data, see if you have any ways to get rid of redundant same data record

I'm creating this for a user and I don't want them to have to rely on pivot tables or subtotals. I could loop thru the cells with a macro to remove the redundant data but I have a couple hundred columns and thousands of rows.

2. the summary should be placed on top of the worksheet, instead of bottom. this can create an easily readible worksheet (user can immediately see the odd cells (i.e. summary) at the top, and user won't need to intentionally search for that, and user can be notified of those odd cells immediately without scrolling)


to tackle the problem, the best way is to sort your data, by FY ascending then BudgetType ascending then Category ascending
add a column to check if, for each record, whether all the fields are identical to the record immediately above it. use 1 and 0 to indicate it

The summary will actually be on a separate worksheet.

finally, sumif or sumproduct works (or better if you can use a new column to accumulate the sum)

The user will actually be grabbing the data from a different workbook and pasting it into the template spreadsheet that contains all of the formulas and calculations. I want her to be able to paste and save and then send the results to the higher ups.

 

[aaalviny]

You said:
1. the root cause of this problem lies on the quality of data, see if you have any ways to get rid of redundant same data record

I'm creating this for a user and I don't want them to have to rely on pivot tables or subtotals. I could loop thru the cells with a macro to remove the redundant data but I have a couple hundred columns and thousands of rows.

2. the summary should be placed on top of the worksheet, instead of bottom. this can create an easily readible worksheet (user can immediately see the odd cells (i.e. summary) at the top, and user won't need to intentionally search for that, and user can be notified of those odd cells immediately without scrolling)


to tackle the problem, the best way is to sort your data, by FY ascending then BudgetType ascending then Category ascending
add a column to check if, for each record, whether all the fields are identical to the record immediately above it. use 1 and 0 to indicate it

The summary will actually be on a separate worksheet.

finally, sumif or sumproduct works (or better if you can use a new column to accumulate the sum)

The user will actually be grabbing the data from a different workbook and pasting it into the template spreadsheet that contains all of the formulas and calculations. I want her to be able to paste and save and then send the results to the higher ups.

so the problem lies on "The user will actually be grabbing the data from a different workbook", not sure you can make it change
I think in data extraction process, duplicate data should clearly be avoided

you can use SUMIF($E:$E, find_str, $G:$G) if you like

 

[Matty]

Try this in D23:

=SUM(IF(FREQUENCY(IF(B2:B20="Actual",IF(C2:C20="G&A Costs",MATCH(D2:D20,D2:D20,0))),ROW(D2:D20)-ROW(D2)+1),D2:D20))

Array entered, i.e. with CTRL+SHIFT+ENTER.

NOTE: This formula will only work if the values in column D are unique. Any duplicate values will only be summed once. This may or may not be a problem, but I suspect it will be!

Matty

 

[fjdurbin]

Try this in D23:

=SUM(IF(FREQUENCY(IF(B2:B20="Actual",IF(C2:C20="G&A Costs",MATCH(D2:D20,D2:D20,0))),ROW(D2:D20)-ROW(D2)+1),D2:D20))

Array entered, i.e. with CTRL+SHIFT+ENTER.

NOTE: This formula will only work if the values in column D are unique. Any duplicate values will only be summed once. This may or may not be a problem, but I suspect it will be!

Matty

WOW! Thank you Matty. You are brilliant!

 

[Matty]

WOW! Thank you Matty. You are brilliant!

No problem. Happy I could help.

However, let me reiterate that the formula will return incorrect results if there are genuine duplicate values existing in column D (any duplicate values will only be counted in the sum once).

Matty

 

[fjdurbin]

After I thought more about it I realized that the risk of duplicate amounts is great. I've build a macro that copies unique rows to a new worksheet. Thanks for the help...JD