SumIf w/ Criteria

davehazle

New Member
Joined
Oct 28, 2005
Messages
28
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
 

Some videos you may like

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.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Oct 28, 2005
Messages
28
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
Joined
Sep 15, 2004
Messages
696
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,118,288
Messages
5,571,321
Members
412,382
Latest member
Langtn02
Top