0

I am using above formulas shown in picture to extract data from column "C". I could extract data of "CE" but where I am making mistake to extract data of "PE". Everything is shown in picture. Could anyone help me out.

One more thing to add here. "CE" and "PE" rows are dynamic. In daily data strike price could add or subtracts. Means adding of strike say 5600, 5700 could possible on both side.

KISHOR BHOIR
  • 89
  • 1
  • 2
  • 11
  • I doubt your formula for "CE" are working. It should also just give you the first value from column C. Probably you are not using the dollar signs in the formula as shown – nightcrawler23 Feb 16 '17 at 06:30
  • Possible duplicate of [EXCEL: Copy cells from colum A -> C ONLY IF column B = X, but only using 1 column](https://stackoverflow.com/questions/44785202/excel-copy-cells-from-colum-a-c-only-if-column-b-x-but-only-using-1-colum) – theforestecologist Jun 27 '17 at 17:02

2 Answers2

0

Use below array formula to F3 cell then drag and down.

=IFERROR(INDEX($C$3:$C$12,SMALL(IF($A$3:$A$12="PE",ROW($C$3:$C$12)-ROW($C$2),""),ROW(1:1))),"")

Below formula to G3 cell

=IFERROR(INDEX($C$3:$C$12,SMALL(IF($A$3:$A$12="CE",ROW($C$3:$C$12)-ROW($C$2),""),ROW(1:1))),"")

Press CTRL+SHIFT+ENTER to evaluate the formula as it is an array formula.

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
0

In F3 use

=SUMPRODUCT(($A$3:$A$12=F$2)*($B$3:$B$12=$E3)*$C$3:$C$12)

In G3 use

=SUMPRODUCT(($A$3:$A$12=G$2)*($B$3:$B$12=$E3)*$C$3:$C$12)

enter image description here

nightcrawler23
  • 2,056
  • 1
  • 14
  • 22