Conditional Calculation

[paulmoss]

Hi

I am hoping you will be able to help me. I am looking to create a conditional calculation. Below is some example data;

Product Code	Description	Sales	Week Number
001AAAA	Mountain Bike Non Painted	100	25
001BBBB	Mountain Bike Painted Red	50	25
001CCCC	Mountain Bike Painted Blue	75	25
002AAAA	Racing Bike Non Painted	100	25
002BBBB	Racing Bike Painted Red	50	25
002CCCC	Racing Bike Painted Blue	75	25
SD001AAAA	Saddle	45	25
HD001AAAA	Handle Bars	66	25

The calculation I want to create is to add the sales of all products that begin with the same number and divide that by the week number. So 001AAAA, 001BBBB and 001CCCC are added together and then divided by the week number, but only on products that end with AAAA. On products that don't end with AAAA the calculation should divide the sales of that product by week number only, and I also need it to divide the sales by week number on any product that begins with a letter.

I hope this makes sense, and you are able to help

Regards

Paul Moss

[TMS]

One way:

=SUMPRODUCT(--(LEFT($A$2:$A$9,3)=LEFT($A2,3)),--($C$2:$C$9))


Regards, TMS

[Richard Buttrey]

Hi Paul,

Does the attached help

First extract a unique list of codes in column E to column H using Data Filter Advanced then copy I2:J2 down as far as necessary. These are your summary numeric & AAAA codes. The other individual sums are in column F

[paulmoss]

Thanks for the prompt response that looks like it will work a treat.