Modifying table formula to extract data by price bands

Hi All

I currently break down my horse racing data by Course and also by Month in two separate tables. I am now wanting to duplicate the same table but extract the data by price points as well. I have attached an example and I need help to revise the formula that currently resides in AR108. Previously I have used SUMPRODUCT but not sure how to modify it for a range of prices. I managed to cobble together a formula in AQ108 which counts the number of bets within a price band but I need to calculate what the return is for each price band. If you see what I mean.

If anyone feels up to it, I could do with the cells AV108, AW108 and AX108 revising as well but that would be an added bonus

Many Thanks

Jonathan

Thanks for looking at it for me but I don't think I've explained myself very well

I need a formula in AS108 (sorry I originally stated AR108) which calculates the return from all the different bets in the price range. Eg, there are 35 bets in the price range 3.00 - 3.99 and I need to calculate how much return I've had from the corresponding data in column P. So I need the formula to add up the returns for all the bets that were in that price range of 3.00 - 3.99. Similar to what the formula in AS92 does.

Apologies for not being clear

Jonathan

Try

In AS108

=IF(AQ108=0,"",SUMPRODUCT(--(K$3:K$297>=LEFT(AP108,FIND("-",AP108)-2)+0),(K$3:K$297<=RIGHT(AP108,FIND("-",AP108)-2)+0)--(N$3:N$297=1),--(M$3:M$297)))

copy down

Modify other ranges accordingly

Hi John

Thanks for taking the time to have a look. I've used the formula but it's not quite giving me what I need. I've attached the sheet with the filled in formula and the numbers can't be right. I think the formula needs to use the values in column P to calculate how much each price band returns.

Thanks again

Jonathan

Change M to P

Well spotted!

Hi Guys

I've used your formula xlnitwit and we seem to be on the right track but not quite there. It returns a value but the totals don't add up so there's a problem somewhere.

I've attached the same sheet with the formula in AS108 included and if you look in column x those are the return values the cells in AS108:AS129 should be returning.

The total value in cell AS130 should match those in cells AS88 and AS104.

All help gratefully received.

Jonathan

Ah, I see what I've done there. Thanks for pointing it out to me. I've changed the formula to read column M instead of P and voila. Perfect!

Many thanks for your help

Jonathan