# SumIf w/ Criteria

#### davehazle

##### New Member
This may be a simple fix, but for the life of me I cant seem to figure it out. I am using the following sumif formula to add up columns of data in B IF the store numbers in column A are the same. In other words. I am adding up dollar amounts in B for each store.
=SUMIF('Master Rollup'!A:A,A1,'Master Rollup'!B:B)

What I am fining though, is that I am getting some false data because some of my data dumps are repeat data. I have figured out that if the value in column C in my tables are the same, then the B data is repeat information.

I suppose what i am trying to do is ad another criteria to the sumif to compare the C values and eliminate instances of duplicates.

any assistance would be great
Thanks

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

##### MrExcel MVP
davehazle said:
This may be a simple fix, but for the life of me I cant seem to figure it out. I am using the following sumif formula to add up columns of data in B IF the store numbers in column A are the same. In other words. I am adding up dollar amounts in B for each store.
=SUMIF('Master Rollup'!A:A,A1,'Master Rollup'!B:B)

What I am fining though, is that I am getting some false data because some of my data dumps are repeat data. I have figured out that if the value in column C in my tables are the same, then the B data is repeat information.

I suppose what i am trying to do is ad another criteria to the sumif to compare the C values and eliminate instances of duplicates.

any assistance would be great
Thanks

Care to post a small sample that shows the problem you are dealing with along with the desired results?

#### davehazle

##### New Member
TV 12.22.xls
ABCDEFGH
1locationbrandmodelold_pricenew_priceMarkdown% Markdownticket_no
21695SHALC45GD4U4699.993313.99138629.49%1500714
33697SHALC45GD4U4699.993478.99122125.98%2836834
43696PHL42PF7320A2999.991779.491220.540.68%601689
53672PANTH50PX50U3999.992799.981200.0130.00%3444961
63672PANTH50PX50U3999.992799.981200.0130.00%3444961
73664PHL42PF7320A2999.991889.99111037.00%3762848
83160PANTH50PX50U3999.992999.99100025.00%1511302
93674SAMHPR50523999.992999.99100025.00%1893234
104118PHL42PF7320A2999.991999.99100033.33%4066151
113744HIT55HDS524699.983699.98100021.28%1379620
124114PANTH50PX50U3999.993060939.9923.50%4253999
133150PHL42PF7320A2999.992089.9991030.33%389493
143672PANTH50PX50U3999.993099.98900.0122.50%3466544
153680PANTH50PX50U3999.993099.98900.0122.50%3466544
163679PANTH50PX50U3999.993099.98900.0122.50%5186919
TV 12.15

In this example, I would be using
=SUMIF('Master Rollup'!A:A,A1,'Master Rollup'!F:F)
However, H% and H6 as well as H14 and H15 are duplicate tickets so I would only want to add the corresponding F value once. Hope this makes sence and you can still help me

#### a7n9

##### Well-known Member
1. Enter this formula in I2 and drag down all the way to the end of the data:=COUNTIF(\$H\$2:\$H2,H2)
2. Enter this formula (Ctrl +Shift+Enter) where you want the sum:=SUM(IF(I2:I16=1,F2:F16,0))

#### Krishnakumar

##### Well-known Member
Hi,

Try,

=SUMPRODUCT((H2:H16<>"")/COUNTIF(H2:H16,H2:H16&""),F2:F16)

HTH

Replies
5
Views
174
Replies
4
Views
121
Replies
4
Views
72
Replies
6
Views
74
Replies
4
Views
132