Sum of unique values with multiple matching criteria

fjdurbin

New Member
Joined
Apr 9, 2009
Messages
28
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

SumUniqueWithMultipleCriteria.jpg
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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

SumUniqueWithMultipleCriteria.jpg
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)
 
Upvote 0
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.
 
Upvote 0
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

alvin_excel_sum_distinct_20130517.PNG
 
Upvote 0
Try this in D23:

Code:
=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
 
Upvote 0
Try this in D23:

Code:
=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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,060
Messages
6,169,860
Members
452,286
Latest member
noclue2000

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top