Formula Help

excel-lent girl

New member
Joined
Jun 23, 2012
Messages
1
Reaction score
0
Points
0
Location
A, A
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,"data1")


But what I want to do now is have excel take all the variances where the value of "data1" is true in a cell and then count the number of times "data2" 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).


Example:


data1 data1 data2 data3 data1
data3 data2 data1 data4 data2
data2 data1 data3 data1 data4
data1 data3 data4 data3 data3
data3 data1 data2 data4 data1
data1 data4 data1 data3 data2


So if I used =COUNTIFS('spread sheet'!1:15,"data1") I'd get a value of 11


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


Is that possible?
 

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
Kinda crude but it gets it done.
 

Attachments

  • COUNTUF_DATA1_DATA2.xlsx
    9.5 KB · Views: 17

CheshireCat

New member
Joined
Dec 30, 2011
Messages
121
Reaction score
0
Points
0
Location
Victoria, Canada
Excel Version(s)
Microsoft Excel 2013
Using Tommy's data sample, you could use an array formula such as:

Code:
=SUM((E10:I15="data1")*(E11:I16="data2"))

Remember to use Ctrl-Shift-Enter to make the formula work as an array formula.

Cheers,
 
Top