I have attached a sample workbook - I am trying to use the aggregate function instead of Subtotal, because I have criteria:
I have tried several iterations of =AGGREGATE(9,3,Sales1*(Prod1="Ties"))
I am relatively new to the use of the function, it would be greatly appreciated if someone could help me solve the problem but also explain what I am doing wrong for future use.
You cannot use aggregate like that unless you are using functions 14 upwards (I think)
How about
=SUMIF(Prod1,"ties",Sales1)
Thanks but the reason I am not using Sumif or Sumproduct, is I need it to operate more like subtotal (in that it skips the other subtotals, lower down)
Unfortunately you cannot use aggregate with option 9 like that.
It wasn't the solution I was looking for but =SUMPRODUCT(SUBTOTAL(9,OFFSET($Y$8,ROW($Y$8:$Y$11)-ROW($Y$8),0)),($X$8:$X$11="Ties")+0) worked for what I was attempting to use aggregate for.
Thank
Glad you sorted it & thanks for the feedback