counting cells in a column if the row above has a certain value in another column

[polgy]

Hi everyone,

I need a formula to count the number of cells in a column only containing a certain value, if they are in the row immediately below the row containing another value in another column.

Here is an example (columns A, B, rows 1-6):

[TABLE="width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]x[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]x[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]x[/TD]
[TD]y[/TD]
[/TR]
</tbody>[/TABLE]

I want to count how many y in B are in a row below the row of a cell containing x in A;
in the above example, this number is 2: the y on row 2 follows the x in row 1, and the y in row 4 follows the x in row 3.

Thanks!

 

[polgy]

@jtakw I actually tried both formulas, but instead of copying and pasting the intervals, I redefined them on my worksheet

Hope it is clear now: I will test this further, but I think both formulas work well for my problem!

Thanks again to both of you!!

 

[jtakw]

Well, I'm/We're glad that it's working for you.

Yes, the formulas will work consistently for your requirements.

There's no contradiction in your request, I believe sheetspread was just perplexed when you said our formulas weren't working and was looking for clarification.

If you have a "large" dataset, I do recommend my COUNTIFS formula over the SUMPRODUCT as it's less resource intensive.

Again, I'm glad it's working for you, post back if you need more assistance.

And You're Welcome, Welcome to the forum.

 

[sheetspread]