**Question description:**

*This user has given permission to use the problem statement for this blog.*

I have a formula which I am trying to append, I would like to add d2:d301 to it. I've inherited the formula so not sure how to add to it
current formula is
=iferror(if(row()<>2,INDEX(arrayformula(filter($P$2:$P301,$C$2:$C301<>"",row($C$2:$C301)=max(if($C$2:$C301=C302,row($C$2:$C301),0)))) ,1),0),0)

Solved by A. H. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
08/07/2018 - 04:28

Hi..

User
08/07/2018 - 04:29

hi

Excelchat Expert
08/07/2018 - 04:29

do you have the data where the formula is being used?

Excelchat Expert
08/07/2018 - 04:29

it will help me in debugging the formula better..

User
08/07/2018 - 04:29

yes but can't share it

User
08/07/2018 - 04:30

has company confidential info

Excelchat Expert
08/07/2018 - 04:30

ohh.. ok.. i understand.. no problem..

Excelchat Expert
08/07/2018 - 04:30

can you explain the context of this formula?

Excelchat Expert
08/07/2018 - 04:31

also are you having this formula in google sheet?

User
08/07/2018 - 04:32

two accounts owners have same product and need file to recognise they are seperate owners colume D has owner name, colume c is product

User
08/07/2018 - 04:32

yes google sheets

Excelchat Expert
08/07/2018 - 04:33

can you please help me recreate a fake scenario of the data on this googlesheet here...

User
08/07/2018 - 04:34

column p tracts all costs of product associated with the buy or sell

Excelchat Expert
08/07/2018 - 04:34

i have got an idea of the formula.. but to debug it will need some data..

User
08/07/2018 - 04:35

ok can you see the sheet to the right?

Excelchat Expert
08/07/2018 - 04:36

yes..

User
08/07/2018 - 04:36

ok I ll type in there

Excelchat Expert
08/07/2018 - 04:41

Hi are you there??

User
08/07/2018 - 04:44

dont worry about sector "ref" it copied over bad. it's fine on mine

Excelchat Expert
08/07/2018 - 04:44

ok.. in which cell are you using this formula?

User
08/07/2018 - 04:45

colunm n is where formula is

Excelchat Expert
08/07/2018 - 04:46

Thx.. for the data.. with this info.. let me link the question posted earlier..

Excelchat Expert
08/07/2018 - 04:46

give me a few mins..

Excelchat Expert
08/07/2018 - 04:49

can you please updated the data for column P.. without the formula?

Excelchat Expert
08/07/2018 - 04:49

i see from the formula its a key column...

User
08/07/2018 - 04:50

you just want raw numbers

Excelchat Expert
08/07/2018 - 04:50

yes..

Excelchat Expert
08/07/2018 - 04:54

ok.. i kinda got the data..

User
08/07/2018 - 04:55

but previous cost should read 0, 0, cause owner is different

User
08/07/2018 - 04:55

for n2 and n3

Excelchat Expert
08/07/2018 - 04:56

i've not yet.. done the formula..

Excelchat Expert
08/07/2018 - 04:56

so now you need to get the previous cost for the same owner?

User
08/07/2018 - 04:56

yes just explaining what I think it

User
08/07/2018 - 04:57

yes each owner should get their own, but right now it sums up regardless of owner it ids just the product

Excelchat Expert
08/07/2018 - 05:00

=iferror(if(row()<>2,INDEX(arrayformula(filter($P$2:$P2,$C$2:$C2<>"",$D$2:$D2=D2,row($C$2:$C2)=max(if($C$2:$C2=C2,row($C$2:$C2),0)))) ,1),0),0)

Excelchat Expert
08/07/2018 - 05:00

can you please paste this updated formula in cell N2?

Excelchat Expert
08/07/2018 - 05:00

in your googlesheet?

Excelchat Expert
08/07/2018 - 05:00

and drag this formula all the way till cell 302..

Excelchat Expert
08/07/2018 - 05:01

drag the formula = at the bottom right corner of the cell.. click that and drag it downwards until cell 302

User
08/07/2018 - 05:02

ok going now

User
08/07/2018 - 05:06

not quite right it add the numbers look way too big.

User
08/07/2018 - 05:07

what i need it to do is filter product, the owner, and then add the cum cost

Excelchat Expert
08/07/2018 - 05:08

also should it be for the same product?

Excelchat Expert
08/07/2018 - 05:08

from cell C1?

User
08/07/2018 - 05:08

yes

Excelchat Expert
08/07/2018 - 05:09

the last formula you shared was checking if product column is not blank..

Excelchat Expert
08/07/2018 - 05:09

ohh ok.. let fix that problem also.

Excelchat Expert
08/07/2018 - 05:09

one question... will the product column be blank??

Excelchat Expert
08/07/2018 - 05:10

based on the data i dont think so..

User
08/07/2018 - 05:10

no product will be not be blank

User
08/07/2018 - 05:10

nor owner

Excelchat Expert
08/07/2018 - 05:10

got it..

Excelchat Expert
08/07/2018 - 05:10

can you please use this updated formula???

Excelchat Expert
08/07/2018 - 05:11

iferror(if(row()<>2,INDEX(arrayformula(filter($P$2:$P2,$C$2:$C2=C2,$D$2:$D2=D2,row($C$2:$C2)=max(if($C$2:$C2=C2,row($C$2:$C2),0)))) ,1),0),0)

Excelchat Expert
08/07/2018 - 05:11

repeat the same step from N2 till N302

User
08/07/2018 - 05:14

no not right. sorry

Excelchat Expert
08/07/2018 - 05:15

hmm.. for sampling.. i'm updating the product and account holder to same ..and testing..

User
08/07/2018 - 05:16

ok

Excelchat Expert
08/07/2018 - 05:18

from the data value of cell N3 should have been $1619.93?

Excelchat Expert
08/07/2018 - 05:18

and value of cell N4 should have been 1834.95?

Excelchat Expert
08/07/2018 - 05:18

correct?

User
08/07/2018 - 05:19

with the original formula yes, but I need it to say 0, then when it is sold in row 4 the previous cost should read 28590

User
08/07/2018 - 05:20

andn row 5 previous cost s/b 1619.93

Excelchat Expert
08/07/2018 - 05:21

hmmm.. you did not mention about using the Buy/Sell ... also.. the formula doesnt take ref. from column "Transaction value with F/X"

Excelchat Expert
08/07/2018 - 05:22

i have only updated the previous formula to include "account Held" column and "Product" column..

User
08/07/2018 - 05:22

not worried about f/x

Excelchat Expert
08/07/2018 - 05:23

so you want only Buy/Sell?

User
08/07/2018 - 05:24

buy sell doen't effect formula, the transacted units have + or -ve that drive it in column m

User
08/07/2018 - 05:24

coumn g

Excelchat Expert
08/07/2018 - 05:25

hmm.. we are left with only 4mins for this session...

Excelchat Expert
08/07/2018 - 05:26

so if i understand the whole requirement correctly.. for a Buy/Sell against an account holder.. you need to the previous max value from column P?

Excelchat Expert
08/07/2018 - 05:26

correct?

User
08/07/2018 - 05:26

yes

Excelchat Expert
08/07/2018 - 05:26

please use the formula..

User
08/07/2018 - 05:26

account and product.

Excelchat Expert
08/07/2018 - 05:26

=iferror(if(row()<>2,INDEX(arrayformula(filter($P$2:$P2,$C$2:$C2=C2,$B$2:$B2=B2,$D$2:$D2=D2,row($C$2:$C2)=max(if($C$2:$C2=C2,row($C$2:$C2),0)))) ,1),0),0)

Excelchat Expert
08/07/2018 - 05:27

i have now included the criteria for account and product...

User
08/07/2018 - 05:27

account holder can buy many products and then sell them later

Excelchat Expert
08/07/2018 - 05:27

the new formula includes the criteria for Type also.. Buy/Sell

Excelchat Expert
08/07/2018 - 05:28

we are running out of time..

User
08/07/2018 - 05:28

right orginal formula only accounted for product not account holder

Excelchat Expert
08/07/2018 - 05:28

marking the session as done..

Excelchat Expert
08/07/2018 - 05:28

please validate the formula.. as previously..

User
08/07/2018 - 05:28

can you help?

Excelchat Expert
08/07/2018 - 05:28

and provide a feedback

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*