SUMIFS / COUNTIFS multiple & conditional criteria

Question

Column A has: Apple Orange Pear Banana Mango multiple times and in random orders.

Column C has: Red Yellow Green Blue Purple corresponding to column A multiple times and in random orders.

I am looking for a formula which counts or sums all instances where Pear immediately follow Apple (i.e. the row below Apple) AND Pear is also Green.

I can return Apple and Pear and even Pear that are Green separately, but I cannot figure out how to return instances of Pear that are Green which immediately follow Apple.

Have attempted multiple formulas using COUNTIFS, SUMIFS, __OFFSET__but cannot find a working combination. Here is a screenshot of sample data. Book2

Answer 1

I solved this problem using

=IF(AND(INDIRECT("A"&ROW()-1)="Apple", B2="Green"),1,0)

where Col A is fruits and Col B is colors. I copied this formula from C2 to the rest of the rows. It basically writes 1 to Col C if the condition is met, otherwise 0. I then took a sum of results.

Answer 2

I think this is what you're looking for, should return true if pear is green and the cell above is an apple.

=IF(EXACT(TRIM(CONCATENATE(A2," ",B2)),"Pear Green"),IF(A1="Apple",TRUE,FALSE),FALSE)