How can I sum values while ensuring no duplicates?

S

sirnickalot

New Member
Joined
Sep 26, 2016
Messages
2
I've thought that the new =maxifs() function could help me here but I do not have access to it yet in my version of excel.
Basically I have a very large data set from which I need to sum some specific values based on a number of criteria. I've used =sumifs() until now, but every so often there are duplicates which cause double or even triple counting and skews the end result.

For example:
[TABLE="width: 899"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]DV[/TD]
[TD]SPID[/TD]
[TD]Location 1[/TD]
[TD]Account 1[/TD]
[TD]Meter 1[/TD]
[TD]Tariff 1[/TD]
[TD]11/19/2015[/TD]
[TD]12/21/2015[/TD]
[TD]12/23/2015[/TD]
[TD]All Other Applicable ESS Riders USD[/TD]
[TD]0[/TD]
[TD]193.97[/TD]
[/TR]
[TR]
[TD]DV[/TD]
[TD]SPID[/TD]
[TD]Location 1[/TD]
[TD]Account 1[/TD]
[TD]Meter 1[/TD]
[TD]Tariff 1[/TD]
[TD]11/19/2015[/TD]
[TD]12/21/2015[/TD]
[TD]12/23/2015[/TD]
[TD]Total KWH[/TD]
[TD]24720[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]DV[/TD]
[TD]SPID[/TD]
[TD]Location 1[/TD]
[TD]Account 1[/TD]
[TD]Meter 1[/TD]
[TD]Tariff 1[/TD]
[TD]11/19/2015[/TD]
[TD]12/21/2015[/TD]
[TD]12/23/2015[/TD]
[TD]Fuel USD[/TD]
[TD]0[/TD]
[TD]594.76[/TD]
[/TR]
[TR]
[TD]DV[/TD]
[TD]SPID[/TD]
[TD]Location 1[/TD]
[TD]Account 1[/TD]
[TD]Meter 1[/TD]
[TD]Tariff 1[/TD]
[TD]11/19/2015[/TD]
[TD]12/21/2015[/TD]
[TD]12/23/2015[/TD]
[TD]Transmission Demand Chg USD[/TD]
[TD]0[/TD]
[TD]98.81[/TD]
[/TR]
[TR]
[TD]DV[/TD]
[TD]SPID[/TD]
[TD]Location 1[/TD]
[TD]Account 1[/TD]
[TD]Meter 1[/TD]
[TD]Tariff 1[/TD]
[TD]11/19/2015[/TD]
[TD]12/21/2015[/TD]
[TD]12/23/2015[/TD]
[TD]Dist Serv Demand Charge USD[/TD]
[TD]47.2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]DV[/TD]
[TD]SPID[/TD]
[TD]Location 1[/TD]
[TD]Account 1[/TD]
[TD]Meter 1[/TD]
[TD]Tariff 1[/TD]
[TD]11/19/2015[/TD]
[TD]12/21/2015[/TD]
[TD]12/23/2015[/TD]
[TD]All Applicable Distribution Riders USD[/TD]
[TD]0[/TD]
[TD]13.11[/TD]
[/TR]
[TR]
[TD]DV[/TD]
[TD]SPID[/TD]
[TD]Location 1[/TD]
[TD]Account 1[/TD]
[TD]Meter 1[/TD]
[TD]Tariff 1[/TD]
[TD]11/19/2015[/TD]
[TD]12/21/2015[/TD]
[TD]12/23/2015[/TD]
[TD]Dist Serv Demand Charge USD[/TD]
[TD]0[/TD]
[TD]1.98[/TD]
[/TR]
[TR]
[TD]DV[/TD]
[TD]SPID[/TD]
[TD]Location 1[/TD]
[TD]Account 1[/TD]
[TD]Meter 1[/TD]
[TD]Tariff 1[/TD]
[TD]11/19/2015[/TD]
[TD]12/21/2015[/TD]
[TD]12/23/2015[/TD]
[TD]Dist Serv Demand Charge USD[/TD]
[TD]0[/TD]
[TD]169.8[/TD]
[/TR]
[TR]
[TD]DV[/TD]
[TD]SPID[/TD]
[TD]Location 1[/TD]
[TD]Account 1[/TD]
[TD]Meter 1[/TD]
[TD]Tariff 1[/TD]
[TD]11/19/2015[/TD]
[TD]12/21/2015[/TD]
[TD]12/23/2015[/TD]
[TD]Sales and Use Surcharge USD[/TD]
[TD]0[/TD]
[TD]15.08[/TD]
[/TR]
[TR]
[TD]DV[/TD]
[TD]SPID[/TD]
[TD]Location 1[/TD]
[TD]Account 1[/TD]
[TD]Meter 1[/TD]
[TD]Tariff 1[/TD]
[TD]11/19/2015[/TD]
[TD]12/21/2015[/TD]
[TD]12/23/2015[/TD]
[TD]Total KWH[/TD]
[TD]24720[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]DV[/TD]
[TD]SPID[/TD]
[TD]Location 1[/TD]
[TD]Account 1[/TD]
[TD]Meter 1[/TD]
[TD]Tariff 1[/TD]
[TD]11/19/2015[/TD]
[TD]12/21/2015[/TD]
[TD]12/23/2015[/TD]
[TD]Electricity Supply KWH[/TD]
[TD]0[/TD]
[TD]631.85[/TD]
[/TR]
[TR]
[TD]DV[/TD]
[TD]SPID[/TD]
[TD]Location 1[/TD]
[TD]Account 1[/TD]
[TD]Meter 1[/TD]
[TD]Tariff 1[/TD]
[TD]11/19/2015[/TD]
[TD]12/21/2015[/TD]
[TD]12/23/2015[/TD]
[TD]Basic Customer Charge USD[/TD]
[TD]0[/TD]
[TD]22.58[/TD]
[/TR]
[TR]
[TD]DV[/TD]
[TD]SPID[/TD]
[TD]Location 1[/TD]
[TD]Account 1[/TD]
[TD]Meter 1[/TD]
[TD]Tariff 1[/TD]
[TD]11/19/2015[/TD]
[TD]12/21/2015[/TD]
[TD]12/23/2015[/TD]
[TD]Late Payment Charge USD[/TD]
[TD]0[/TD]
[TD]7.76[/TD]
[/TR]
[TR]
[TD]DV[/TD]
[TD]SPID[/TD]
[TD]Location 1[/TD]
[TD]Account 1[/TD]
[TD]Meter 1[/TD]
[TD]Tariff 1[/TD]
[TD]11/19/2015[/TD]
[TD]12/21/2015[/TD]
[TD]12/23/2015[/TD]
[TD]Total KWH[/TD]
[TD]24720[/TD]
[TD]34.14[/TD]
[/TR]
</tbody>[/TABLE]


I would be looking for the value in column K associated with "Total KWH" for this date, location, account, meter, etc. but would only want the value 24720 returned once.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Can you put in a helper column, for example in column M?
Formula for column M2 (drag down):
Code: 
=K2/IFERROR(COUNTIFS(Same criterias as in your SUMIFS),1)
Then just SUMIFS column M instead...
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Lookup+sum+max
Replies
2
Views
544
aarky
A
U
  • Solved
Help with Index and multiple matches that include sum
Replies
2
Views
336
usnapoleon
U
W
  • Question Question
Dynamic XIRR for sum of CF's with Offset
Replies
3
Views
4K
DRSteele
DRSteele
R
  • Solved
VBA Question: How to find duplicate rows, and sum values after finding duplicates?
2
Replies
14
Views
2K
Alex Blakenburg
A
V
  • Question Question
How can I prevent this behaviour from forecast.ets / forecast.linear?
Replies
0
Views
340
VirtualJr
V

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

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