SumIf w/ Criteria

[davehazle]

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

 

[Aladin Akyurek]

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]

TV 12.22.xls
	A	B	C	D	E	F	G	H
1	location	brand	model	old_price	new_price	Markdown	% Markdown	ticket_no
2	1695	SHA	LC45GD4U	4699.99	3313.99	1386	29.49%	1500714
3	3697	SHA	LC45GD4U	4699.99	3478.99	1221	25.98%	2836834
4	3696	PHL	42PF7320A	2999.99	1779.49	1220.5	40.68%	601689
5	3672	PAN	TH50PX50U	3999.99	2799.98	1200.01	30.00%	3444961
6	3672	PAN	TH50PX50U	3999.99	2799.98	1200.01	30.00%	3444961
7	3664	PHL	42PF7320A	2999.99	1889.99	1110	37.00%	3762848
8	3160	PAN	TH50PX50U	3999.99	2999.99	1000	25.00%	1511302
9	3674	SAM	HPR5052	3999.99	2999.99	1000	25.00%	1893234
10	4118	PHL	42PF7320A	2999.99	1999.99	1000	33.33%	4066151
11	3744	HIT	55HDS52	4699.98	3699.98	1000	21.28%	1379620
12	4114	PAN	TH50PX50U	3999.99	3060	939.99	23.50%	4253999
13	3150	PHL	42PF7320A	2999.99	2089.99	910	30.33%	389493
14	3672	PAN	TH50PX50U	3999.99	3099.98	900.01	22.50%	3466544
15	3680	PAN	TH50PX50U	3999.99	3099.98	900.01	22.50%	3466544
16	3679	PAN	TH50PX50U	3999.99	3099.98	900.01	22.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]

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]

Hi,

Try,

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

HTH