Results 1 to 3 of 3

Thread: Formula Help

  1. #1
    Neophyte excel-lent girl's Avatar
    Join Date
    Jun 2012
    Location
    A, A
    Posts
    1
    Articles
    0

    Question Formula Help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Kinda crude but it gets it done.
    Attached Files Attached Files

  3. #3
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    118
    Articles
    0
    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,

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •