Sum Specific cells if match criteria in another cell

[arnorian]

Hi,

I have over 8000 line excel report which lists all items in a quotes. I need to add column for "Extended List Price" only for the lines that are for the same quote

Example: I need for the code to be able to scan my report and average the discount % but only for the same quotes . Currently My code would have added up and averages all the Discount % listed below.

Quote Number	Extended Amount	Extended Amount Net of Incentive	Discount %	Discount for Entire Quote
00400318	$280000	$140000	50%
00300418	$400000	$200000	50%
00300418	$500000	$250000	50%
00300418	$600000	$60000	90%
00300418	$25000	$0	100%	(50+50+90+50+100)/5 = 68%
12345678	$280000	$140000	50%

<tbody>
</tbody>

Thank You

 

[jeffreybrown]

You show five %s for quote number 00300418, but the data shows only four.

Anyway, maybe in E2 copied down...

=IF(A2<>A3,SUMIF($A$2:$A$8000,A2,$D$2:$D$8000)/COUNTIF($A$2:$A$8000,A2),"")

 

[arnorian]

Hi,

Sorry looks like I copied "50" an extra time.

Let me try that and see if that works.

Thank You

 

[jeffreybrown]

Actually, I should have just gone with...

=IF(A2<>A3,AVERAGEIF($A$2:$A$8000,A2,$D$2:$D$8000),"")

 

[pgc01]

Hi arnorian
Welcome to the board

I'm sorry but your post does not make any sense to me. What is the meaning of adding the discount % and averaging it?

The way you have it, if you buy $1 and get 90% discount, and then $1,000 and get 10% discount, then (90%+10%)/2, you have on average 50% discount. Does this make sense to you?

 

[arnorian]

Hi pgc01,

Well what I'm really trying to accomplish is to find a code that can scan my file and then if Quote Numbers match then add all of the List Prices and Discounted Final Prices then calculate "Final Discount" provided. (There are some instances of 1 time discount thats being applied after standard discounts are used.

Problem that I'm running into is finding code that will match Quote Numbers for the entire 8000 lines of the report.