# In Excel - Sum amount with multiple criteria and excluding duplicates from sum

## In Excel - Sum amount with multiple criteria and excluding duplicates from sum

In Excel 2010, I have 2 tabs:

Summary Tab

Bank Tab

In Bank Tab:

Col A Col B Col C Col D

SC New acct123 500

SC New acct123 500

SC New acct123 100

SC Renew acct123 200

GA New acct125 100

GA New acct125 100

SC Renew acct126 600

In the Summary Tab:

Col A Col B Col C

SC New ? formula I have so far: =SUMIFS(Bank!$D:$D,Bank!$A:$A,Summary!$A$2,Bank!B:B,Summary!B2)

I want to add col D (from Bank tab) using multiple distinct criteria based on Column A, B, C, and D (from the Bank tab), but not summing any of the duplicates.

The result in Col C (in the Summary tab) should be: 600

The formula I have above is incomplete, it does not filter out the duplicates.

Visit publisher's web-site: In Excel - Sum amount with multiple criteria and excluding duplicates from sum

## Related Tutorials

### Sum column excluding duplicates

I'm trying to sum a column, excluding the duplicates. For example, the following numbers are in column D:

The total if the duplicate 2 is not included is 6.

My brain seems to still be in the off position today.

### sum duplicates

In attached,I have 6 sheets with part numbers and totals.

In master sheet I was hoping to add all the totals from duplicate part numbers from the 6 sheets.

I was looking for a formula that would look down from A3 to A64 and when dragged down in D column would show all part numbers excluding the duplicates.

### Combining or Optimizing Multiple SUMPRODUCTS

I have a cell that requires the SUM of 5 SUMPRODUCTS.

The 5 SUMPRODUCTS are checking for criteria in the same column (ex. A) and summing the quantities in the same column (B).

It's currently setup like this,

[criteria 1]*[sum criteria 1]

[criteria 2]*[sum criteria 2]

[criteria 3]*[sum criteria 3]

[criteria 4]*[sum criteria 4]

[criteria 5]*[sum criteria 5]

Since it's summing the quantity in the same column, in theory, couldn't I combine it?

[criteria1]*[criteria2]*[criteria 3]*[criteria 4]*[criteria 5]*[sum quantity column B]?

I'm trying to optimize this so it's faster calculation in excel.

### Sum Mutliple criteria Using the mid Function

Please refer to attachment for specific data

I need two formulas, one to sum hours or dollars based on each specific discipline while excluding cost codes for that discipline where the cost code at =mid(cost code,4,3) which will be 88. The second one will sum all the 88 for each discipline.

I was trying to use a sumproduct which has worked well in multiple criteria sums but I can't figure out how to use the mid formula along with it.

### Sum range excluding multiple words

In a Colombo voice. . . "Just one more thing please sir". . .

Is there a way of using sumif to tell excel

if the cell contains neither "petrol" or "toll" then put total in a different cell.

Im sorry to be a right typical noob but I can't seem to understand a lot of the posts here.

There a bit too technical for me.

## Related Applications & Scripts

### PayPal Fee Calculator

Here's a useful tool you can use to calculate your PayPal fees. It includes many advanced options such as choosing the source and destination countries, choosing which PayPal Fee Rate you want to use, and choosing whether or not to include the 2.5% currency conversion fee (for multiple currency transactions). You can also use it to "reverse" calculate your PayPal fees to determine exactly how much a person should send you so that after fees are removed you actually end up with the amount you want. I use it all the time myself when I'm selling on eBay, and I'm trying to gauge that "magic" amount to charge so that after all fees have been removed I actually end up with the amount I want. For example, did you know that to actually receive $75 in your PayPal account someone would have to send you exactly $77.55? And the way you can find out that exact amount is by using a "reverse" paypal calculator such as this. Saves me a lot of time! Hope it will for you too. Any feedback, comments, and suggestions are welcome. Please vote if you find this useful.

### EZ-Shop complete e-commerce system

EZ-Shop is a simple out of the box e-Commerce solution aimed at small startups and independant retailers looking to get into online trade. The system was initially designed to be simple and easy to use but with many features that more complex packages lack.

Some of its features are:

Multiple categories/sub categories, Easily customisable layout just edit the CSS, WYSIWYG editors for all pages to tailor the content to your liking with no prior HTML knowledge required, Discount codes % based and fixed discount including multiple, single or unlimited use, Account history and order tracking, Wish list and email to friend wishlist, Special offers system, Gift bundle system, Infinite amount of products, Small/Medium/Large product images, Multiple manufacturer and brand support, Multiple shipping options, Paypal integration, Order quantity/stock levels and control, Customer newsletters

### Guess And Win Script

You need to guess the number (between 1-1000). When you come first time to the site you have 1 chance to guess the number, if you guess right you transfer to information page and you can prize from our Prizes page, but if you guess wrong, you dont have another chance to guess the sum.

You can get more chances by referring your friends, for 3 people that you refer you will get 1 chance to guess the number, or if you dont want to refer your friends you can donate the site by paypal, if you donate 1$ you get 1 chance to guess the number, if you donate 5$ you get 5 chances to guess the number. You have multiple revenue sources: Adsense, Adbrite, ClickSor, CPALead, etc, and donations by Paypal.

### Loan Spread Repayment Calculator

The Loan Spread Repayment Calculator is a calculator which shows you the amount paid by you for a loan for a range of interest rates in form of a spreadsheet.

It calculates the amount to be paid for a loan for a given principal from 3 % to 25 % at increment of 0.25 %. It also shows the payment amount for all interest rate when principal is increased by a certain value. User can adjust the increment value for principal. So, you can compare 150 types of loans at a time.

### Nature Calls, full XML website

Intro Nature calls is a XML based website with easy editing. Its best used for a simple project, maybe if your a photgrapher and you want to make multiple galleries with an extensive amount of text.

Features:

- Easy editing with XML

- HTML tags allowed in the xml by using CDATA

- Full vector so scale it all you want

- No maximum in the amount of news you want to add

- Pick your choice, you want a gallery or you want a newslist

- You can add an image gallery on every page

- Add html images to the Text

- Add html links to the text