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!

 

[sheetspread]

Excel 2010
	A	B
1	x
2		y
3	x	y
4	x	y
5
6	x	y
7
8		2
Sheet10

Cell Formulas
Range	Formula
B8	=SUMPRODUCT(--($A$1:$A$6="x"),--($B$2:$B$7="y"))

 

[jtakw]

Hi,

I think you can accomplish this using just COUNTIFS:

Book1
	A	B	C
1	x		2
2		y
3	x	y
4	x	y
5
6	x	y
Sheet149

Cell Formulas
Range	Formula
C1	=COUNTIFS(A1:A6,"x",B2:B7,"y")

 

[polgy]

Excel 2010

	A	B
x
y
x	y
x	y
x	y

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

</tbody>

Sheet10​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B8[/TH]
[TD="align: left"]=SUMPRODUCT(--($A$1:$A$6="x"),--($B$2:$B$7="y"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

The double unary operator (--) converts the non numeric values x and y as 1; but the sum of the products in the columns gives 3, not 2: (1*0+0*1+1*1+1*1+1*1)=3. This formula counts the times when x and y occur on the same row. The same thing could have obtained by countifs($A$1:$A$6,"x",$B$2:$B$7,"y").
Sorry I don't need this.

 

[polgy]

If I use this formula the result is 3, not 2.

Maybe I did not explain my problem clearly. I do not need to count the occurrences of x and y when they occur on the SAME row.

I need to count the occurrences of y when y occurs on the row BELOW the row where x occurs

See also my reply to the other suggestion to use sumproduct.

 

[sheetspread]

I agree countifs is better, but the result was also 2 in my example (???)

the y on row 2 follows the x in row 1, and the y in row 4 follows the x in row 3

I do not need to count the occurrences of x and y when they occur on the SAME row.

Seems contradictory, can you explain?

 

[jtakw]

2 things, first, I'm not understanding your response.

Second, both sheetspread and my formula works for your sample, neither formula counts the times when x and y occur on the "same" row, both formulas are "offset" by one row, have you tried them?

 

[jtakw]

Here's a larger sample.

Both my formula and sheetspread's are shown, and are producing the correct result of 4.

Look closely, if the formulas were counting x and y on the "same" row, it would have been 3.

Book1
	A	B	C	D	E
1	x		4		4
2		y	My formula		Sheetspreads
3	x	y
4	x	y
5
6	x	y
7	x
8		y
9	x
10
11		y
12	x
13		y
14		y
15	x
Sheet149

Cell Formulas
Range	Formula
C1	=COUNTIFS(A1:A15,"x",B2:B16,"y")
E1	=SUMPRODUCT(--($A$1:$A$15="x"),--($B$2:$B$16="y"))

 

[jtakw]

@sheetspread No worries, I think OP just took a look at the formulas we provided and immediately dismissed it without Even trying them, sometimes Posters just don't realize some of the "tricks" we use, there's a lot more to an otherwise ordinary looking formula, I've had similar responses with a column offset SUMIF solution where the poster just plainly said "This is Not what I want", told me that I didn't understand what he wanted, and don't bother posting in his thread anymore...

Have a Nice Day.

 

[polgy]

sorry I did not notice the two intervals are offset by one row... is this sufficient to tell excel to consistently search on row lower than in the other column... that is fabulous!
Now it seems to work: I will test the formula with other datasets but it seems it works.

For the apparent contradiction, I am unsure about what you're saying...

the y on row 2 follows the x in row 1, and the y in row 4 follows the x in row 3
refers to the calculation I need: counting y when y is on the row (row 2) below the row that contains x (row 1)

I do not need to count the occurrences of x and y when they occur on the SAME row
refers to the calculation I thought your formula did (which it doesn't, since the two intervals are offset by one row!): I thought the formula was:

=SUMPRODUCT(--($A$1:$A$6="x"),--($B$1:$B$6="y"))

instead of
=SUMPRODUCT(--($A$1:$A$6="x"),--($B$2:$B$7="y"))