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.

[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]Quote Number[/TD]
[TD]Extended Amount[/TD]
[TD]Extended Amount Net of Incentive[/TD]
[TD]Discount %[/TD]
[TD]Discount for Entire Quote[/TD]
[/TR]
[TR]
[TD]00400318[/TD]
[TD]$280000[/TD]
[TD]$140000[/TD]
[TD]50%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00300418[/TD]
[TD]$400000[/TD]
[TD]$200000[/TD]
[TD]50%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00300418[/TD]
[TD]$500000[/TD]
[TD]$250000[/TD]
[TD]50%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00300418[/TD]
[TD]$600000[/TD]
[TD]$60000[/TD]
[TD]90%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00300418[/TD]
[TD]$25000[/TD]
[TD]$0[/TD]
[TD]100%[/TD]
[TD](50+50+90+50+100)/5 = 68%[/TD]
[/TR]
[TR]
[TD]12345678[/TD]
[TD]$280000[/TD]
[TD]$140000[/TD]
[TD]50%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
















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.