IF condition is not met, then check on next row

[imegut0421]

Hi. Help please. I want to summarized all the "Apples" to be inputted in consecutive rows. I attached sample data where cell A3= Apples, A4=Apples, A5=Banana, A6= Banana, A7= Banana and A8=Apples. Using the if then formula on column D, it will go like this:

D3= Apples
D4= Apples
D5= 0
D6= 0
D7= 0
D8 = Apples

But I want my report to be:

D3= Apples
D4= Apples
D5= Apples (being the D5 to D7 is zero, so it should jump to the next row with Apples).

Please help. Thanks.

 

[shrivallabha]

This is fairly straightforward if you have FILTER formula available to you which will work like below. In cell E3:
=FILTER(D3:D10,A3:A10="Apples")

If you do not have access to FILTER formula or you need to maintain compatibility with older versions of Excel then you can use below Array formula (to be committed by pressing CTRL+SHIFT+ENTER) and copy down as much needed.
=IFERROR(INDEX($C$1:$C$10,SMALL(IF($A$1:$A$10="Apples",ROW($A$1:$A$10),10^10),ROWS($A$1:$A1))),"")

 

[imegut0421]

This works!. Thanks!

 

[Kevin@Radstock]

Hi

If you do away with columns C & D, you can use the following.

=FILTER(A3:A10&"-"&B3:B10,A3:A10="Apples")

 

[imegut0421]

Hi. I appreciate all the help you extended. Thank you.

Ahm, can I request for one more favor? Using the "if then" formula, I can't get the result that I want on columns F, G , and H. I do not know what I am doing wrong.. so, please,, help me on this too.

Thanks in advance.

 

[imegut0421]

Sorry, I forgot to mention. The answer to column F, G and H, must be the values in column "C".

 

[imegut0421]

Hi

If you do away with columns C & D, you can use the following.

=FILTER(A3:A10&"-"&B3:B10,A3:A10="Apples")

there is no =Filter in my excel.

 

[vletm]

imegut0421
Would You try to use basic Filter-options ... without any formulas?
... You could get many variations as You need.
eg