Is this array formula preventing me from adding extra regular formulas?

[RockandGrohl]

Hi all, I'm using the following array formula to
A) Lookup the cost of a campaign, say, £20,000
B) Find out the number of unique products in a tour based on columns B, C & D (Imagine taking a group of data and then doing a remove-duplicates taking into account B, C & D as the duplicate criteria, you may end up with 600 lines reduced to 500 unique products
C) Finally, I find out which products are duplicated again based on B, C & D with a countif:

={(VLOOKUP($C143,'Campaign Database'!$A:$G,7,0)/SUM(IF($C$3:$C$762=$C143,(1/COUNTIFS($B$3:$B$762,$B$3:$B$762,$C$3:$C$762,$C$3:$C$762,$D$3:$D$762,$D$3:$D$762)),0)))/COUNTIFS($B$3:$B$762,$B143,$C$3:$C$762,$C143,$D$3:$D$762,$D143)}

By the way, B) needs to be applied with an array formula.

What's happening is that it's successfully looking up the campaign price of £20,000, working out that this chunk of 600 lines has 500 unique products (so dividing £20,000 by 500 to get £40 per product as a baseline, then when this happens:

	A	B	C	D	E
1	Blank	Date	Campaign	Product	Price
2		04/04/2021	1. Donk	Bournemouth	£40
3		11/04/2021	1. Donk	Bournemouth	£20
4		11/04/2021	1. Donk	Bournemouth	£20
5		18/04/2021	1. Donk	Bournemouth	£20
6		25/04/2021	1. Donk	Bournemouth	£20
7		25/04/2021	1. Donk	London	£40

Hopefully that's clear, the Bournemouth Product appears 5 times, but it has three unique products, so 3 x £40 per product is £120 and that is the sum value of each Bournemouth product. Likewise, London appears just once so it has a single value of £40.


What's not working is the countif that determines how many products are there. Because when I use an array to go down, whether I'm on line 143 or 255 or 680, the line referenced is always $C143 or $B143 etc.

How do I make this work without adding helper columns? Thank you.

 

[RockandGrohl]

Ah. If I put the formula in one cell and drag it down, it works. But when I apply the formula to a whole range with VBA:

Range(Cells(Lastrowsd + 1, "G"), Cells(Lastrowsd + LastrowCT - 2, "G")).FormulaArray = "=(VLOOKUP(RC3,'Campaign Database'!C1:C7,7,0)/SUM(IF(R3C3:R" & Lastrowsd + LastrowCT - 2 & "C3=RC3,(1/COUNTIFS(R3C2:R" & Lastrowsd + LastrowCT - 2 & "C2,R3C2:R" & Lastrowsd + LastrowCT - 2 & "C2,R3C3:R" & Lastrowsd + LastrowCT - 2 & "C3,R3C3:R" & Lastrowsd + LastrowCT - 2 & "C3,R3C4:R" & Lastrowsd + LastrowCT - 2 & "C4,R3C4:R" & Lastrowsd + LastrowCT - 2 & "C4)),0)))/COUNTIFS(R3C2:R" & Lastrowsd + LastrowCT - 2 & "C2,RC2,R3C3:R" & Lastrowsd + LastrowCT - 2 & "C3,RC3,R3C4:R" & Lastrowsd + LastrowCT - 2 & "C4,RC4)"

It keeps the cell reference the same.

I can't really do range.filldown as I'm filling in certain gaps of data (LastrowSD might be cell 143 and LastrowCT - 2 is cell 400, out of a range of 800 cells, so it's effectively plugging a gap instead of going down to the bottom.

 

[RockandGrohl]

Belay that remark, Filldown works within a range:

    Range("G" & Lastrowsd + 1).FormulaArray = "=(VLOOKUP(RC3,'Campaign Database'!C1:C7,7,0)/SUM(IF(R3C3:R" & Lastrowsd + LastrowCT - 2 & "C3=RC3,(1/COUNTIFS(R3C2:R" & Lastrowsd + LastrowCT - 2 & "C2,R3C2:R" & Lastrowsd + LastrowCT - 2 & "C2,R3C3:R" & Lastrowsd + LastrowCT - 2 & "C3,R3C3:R" & Lastrowsd + LastrowCT - 2 & "C3,R3C4:R" & Lastrowsd + LastrowCT - 2 & "C4,R3C4:R" & Lastrowsd + LastrowCT - 2 & "C4)),0)))/COUNTIFS(R3C2:R" & Lastrowsd + LastrowCT - 2 & "C2,RC2,R3C3:R" & Lastrowsd + LastrowCT - 2 & "C3,RC3,R3C4:R" & Lastrowsd + LastrowCT - 2 & "C4,RC4)"
    Range(Cells(Lastrowsd + 1, "G"), Cells(Lastrowsd + LastrowCT - 2, "G")).FillDown

As has happened a few times before, I end up solving my own problem and using this forum as some kind of perverse diary to log my thoughts in order to think of a solution. Apologies.