CountIfs + ?

[excel-lent girl]

I'm trying to do something with excel and I'm not quite sure it's possible.

I have data on a spread sheet ranging from 1:15

One step I have taken in what I want to do is having a formula to count the number of time a certain data input shows up:

=COUNTIFS('spread sheet'!1:15,"data 1")

But what I want to do now is have excel take all the variances where the value of "data 1" is true in a cell and then count the number of times "data 2" shows up in the cell below it... so that it will count the number of times 1 is followed by 2 (or 3 or 4 etc).

Is this possible?

Thanks!

 

[Aladin Akyurek]

I'm trying to do something with excel and I'm not quite sure it's possible.

I have data on a spread sheet ranging from 1:15

One step I have taken in what I want to do is having a formula to count the number of time a certain data input shows up:

=COUNTIFS('spread sheet'!1:15,"data 1")

But what I want to do now is have excel take all the variances where the value of "data 1" is true in a cell and then count the number of times "data 2" shows up in the cell below it... so that it will count the number of times 1 is followed by 2 (or 3 or 4 etc).

Is this possible?

Thanks!

Try to change

"data 1"

to

"data *".

 

[excel-lent girl]

Try to change

"data 1"

to

"data *".

That won't return the information I need.

Example:

data 1 data 1 data 2 data 3 data 1
data 3 data 2 data 1 data 4 data 2
data 2 data 1 data 3 data 1 data 4
data 1 data 3 data 4 data 3 data 3
data 3 data 1 data 2 data 4 data 1
data 1 data 4 data 1 data 3 data 2

So if I used =COUNTIFS('spread sheet'!1:15,"data 1") I'd get a value of 11
If I used your suggestion =COUNTIFS('spread sheet'!1:15,"data *") I'd get a value of 30

What I want is a formula that will find each "data 1" and then look beneath it to see if the cell below it contains "data 2"... in this case it should return a value of 3

 

[shift-del]

Hi

	A	B	C	D	E	F	G	H	I
1	data 1	data 1	data 2	data 3	data 1
2	data 3	data 2	data 1	data 4	data 2
3	data 2	data 1	data 3	data 1	data 4
4	data 1	data 3	data 4	data 3	data 3
5	data 3	data 1	data 2	data 4	data 1
6	data 1	data 4	data 1	data 3	data 2
7
8							data 1	data 2	3

<colgroup><col style="width: 28ptpx"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"></colgroup><tbody>
</tbody>

Zelle	Formel
I8	=COUNTIFS(A1:E6,G8,A2:E7,H8)

<colgroup><col style="width: 40ptpx"><col></colgroup><tbody>
</tbody>

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>

 

[Marcelo Branco]

Maybe something like

=COUNTIFS(1:15,"data 1",2:16,"data 2")

M.

 

[excel-lent girl]

Hi

	A	B	C	D	E	F	G	H	I
1	data 1	data 1	data 2	data 3	data 1
2	data 3	data 2	data 1	data 4	data 2
3	data 2	data 1	data 3	data 1	data 4
4	data 1	data 3	data 4	data 3	data 3
5	data 3	data 1	data 2	data 4	data 1
6	data 1	data 4	data 1	data 3	data 2
7
8							data 1	data 2	3

<tbody>
</tbody>

Zelle	Formel
I8	=COUNTIFS(A1:E6,G8,A2:E7,H8)

<tbody>
</tbody>

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>

I'm sorry, this doesn't make much sense... and it's not the area I want to have targeted. I want all data looked at from rows 1-15.. because on my actual worksheet there is untold columns of information to sort through. I do not have the time to change the formula each time a new column is added.

 

[excel-lent girl]

Maybe something like

=COUNTIFS(1:15,"data 1",2:16,"data 2")

M.

It looks like this is working! Thank you!