Count consecutive duplicate values and then divide by that number

Hi,

Apologies in advance if this isn't completely clear, I'm new to the forum so not completely sure on all the format rules for these posts!

I have a set of data that looks a little like the below:

Company	Cost Type	Number of Sales	Rate	Spend
Sky	CPS	10	£30	£300
Sky	CPS	5	£20	£100
Skoda	Fixed	15	£200	£200
Skoda	Fixed	10	£200	£200
Skoda	Fixed	12	£200	£200
Samsung	CPS	10	£10	£100
Volkswagen	Fixed	3	£500	£500
Volkswagen	Fixed	4	£500	£500

I already have a nice formula that looks at what the cost type is and then calculates the spend correctly for CPS (Cost Per Sale) records. The difficulty I have is that I want to be able to accurately recognise the Fixed price records. In my table you can see for example we have 3 consecutive records for Skoda for a fixed price deal they have for £200. In reality this doesn't mean my spend for that company should be £600 though, it's a fixed deal so it should be £200 total spend for all their sales here, which means I want my Spend column to take into account how many consecutive rows have Skoda in column A and then divide the 'Rate' of £200 by that figure. This would mean Skoda's spend per row should actually be £66.66 (200/3). Is there a formula that could do this for me?

Please ask if there's anything I can do to clarify this.

Try in E2 and copy down

=IF(B2="Fixed",D2/COUNTIFS($A$2:$A$9,$A2,$B$2:$B$9,$B2),$C2*$D2)

Thanks John, that worked perfectly for my example. Could you explain for the less technically minded (me!) what exactly the formula is doing here to perform this calculation?

Best,

Jack

=IF(B2="Fixed",D2/COUNTIFS($A$2:$A$9,$A2,$B$2:$B$9,$B2),$C2*$D2)

We check if the "Cost Type" (B2) is "fixed": if TRUE, then we divide rate (D2) by the number "fixed" for a given company


COUNTIFS($A$2:$A$9,$A2,$B$2:$B$9,$B2) counts the matches on Company (A2) AND "Fixed" (B2) in the whole range so for Skoda we get 3 and Volkswagen we get 2.

So even the if the data was not "sorted" we should still get the correct results.

If Cost type is not Fixed (FALSE on IF test), we calculate "Number of Sales" x "Rate"

Hope this helps.

=IF(B2="Fixed",D2/COUNTIFS($A$2:$A$9,$A2,$B$2:$B$9,$B2),$C2*$D2)

We check if the "Cost Type" (B2) is "fixed": if TRUE, then we divide rate (D2) by the number "fixed" for a given company


COUNTIFS($A$2:$A$9,$A2,$B$2:$B$9,$B2) counts the matches on Company (A2) AND "Fixed" (B2) in the whole range so for Skoda we get 3 and Volkswagen we get 2.

So even the if the data was not "sorted" we should still get the correct results.

If Cost type is not Fixed (FALSE on IF test), we calculate "Number of Sales" x "Rate"

Hope this helps.

Perfect, thanks again John.