Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16

Thread: Count.ifs with a lot of conditions

  1. #1

    Count.ifs with a lot of conditions



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

    Dear members,

    I am currently using Excel for my bachelor thesis and I'm doing research for a company that organizes events. They want to know which events trigger people to come again to another event. So I made a database with 4000 records (persons) and 100 events, and I used 1 when they were present and a 0 when they were absent. Now I want Excel to calculate for example, how many people went from event 3 to event 36. I know this is possible with Count.ifs and then 3 = 1, 4-35 = 0 and 36 = 1. But I can only make this work when I write down all 36 conditions. I think this can be done a lot easier, but I can't make it work so far. There must be a way to use sum (event4-35 = 0) or something.

    I'm sorry I can't attach the document, as it contains sensitive information.

    Thanks in advance!

    Dijkhouse

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Assuming each event is in it's own column with 1's and 0's listed down the column, can you not just enter a sum at the bottom? For example, if the last row is 4000, then in B4001 enter =SUM(B2:B4000) and copy it across the columns to get the respective column sums.

    If it is more complicated then that, I suggest you upload a sample file that has a facsimile sample of what you are looking at (remove the sensitive info).


  3. #3
    Thank you for your reply.

    I think it is a little bit more complicated than that. I made an example to show what I mean. Let's say that company has 10 events, then I want to know how the people move through the events. This means I have to calculate all possible arrows from an event. So there are 9 possible arrows from event 1, 8 from event 2 etc. And I want excel to calculate the value of those arrows.

    I attached a sample file to clarify my problem.

    Thanks in advance.

    DijkhouseDijkhouse - Example for forum.xlsx

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Can you please re-upload and show some expected results? I am trying to understand what you mean by "Possible arrows".


  5. #5
    Thanks for your reply.

    I calculated the first column in the matrix. As you can see in the formulas, they get quite long when calculating the people that come at event 1 and then again at event 10, and in the real research there is also an event 100.

    I'll try to explain what i mean with arrows as well. I mean arrows like arrows in a flowchart. People arrive at some event in time and they might go to another event later in time, or not. The purpose of this research is to find out which events are more effective to trigger people to come back. So when a particular person goes to event 1, 5 and 9, then the value of the arrow from event 1 to 5 and from 5 to 9 is 1. When a second person goes from 1 to 5 to 8, then the value of the arrow from 1 to 5 is 2, from 5 to 8 is 1 and from 5 to 9 is also 1. And I want to do this now for 4000 persons and 100 events with a formula as simple as possible (so with the least conditions as possible, instead of 100 conditions when calculating the arrow from 1 to 100, the people who only attend event 1 and 100).

    It's mainly about this formula: =Count.ifs(B2:B30;1;C2:C30;0;D230;0;E2:E30;0;F2:F30;0;G2:G30;0;H2:H30;0;I2:I30;0;J2:J30;0;K2:K30;1). This formula has 10 conditions, but I think all the statements with condition "0" in this formula, C2:C30 untill J2:J30, can be merged into 1 condition.Dijkhouse - Example for forum.xlsx

    I hope this clarifies it for you. I added a new sample file. Please don't hesitate to ask any other question.

    Thanks.

    Dijkhouse

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    I can't think of any real simplification at the moment.... I will have to think about it. I am leaving for the day. I will try again tomorrow, but if anyone else has ideas, then they are welcome to contribute.


  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    I think to make this more efficient, you will need to create a helper column that concatenates all the event results for each person to create a pattern of 0's and 1's that you can reference.

    In order to concatenate 100 columns, however, you will need a User Defined Function.

    I have used the following function:

    Code:
    Function aconcat(a As Variant, Optional sep As String = "") As String
    ' Harlan Grove, Mar 2002
    Dim y As Variant
    
    If TypeOf a Is Range Then
    For Each y In a.Cells
    aconcat = aconcat & y.Value & sep
    Next y
    ElseIf IsArray(a) Then
    For Each y In a
    aconcat = aconcat & y & sep
    Next y
    Else
    aconcat = aconcat & a & sep
    End If
    
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
    End Function
    To add the function to the work book, hit ALT+F11, then go to Insert|Module and paste the code into the editor.

    Now go back to the worksheet and enter formula in next empty column (in your sample, column L).

    =aconcat(B2:K2)

    copied down.

    Now you can use a simple COUNTIF() formula to look up and count the pattern.

    An example in Z2 of pattern: 1000000001

    Then the formula in AA2 would be:

    =COUNTIF($L$2:$L$30;Z2) which can be copied down to count other patterns.

    Hope this helps.
    Attached Files Attached Files


  8. #8
    This definitely helps a lot! I am now able to do most of the calculations.

    There is only one thing that I am not able to fix. When I want the count.if formula to count all the people that go from event 3 to 5, I use the pattern: **101*****. But when I use this pattern, I get exactly the same answer as when I want to count the people from event 4 to 6: ***101****. I also tried to use 00101*****, but then the formula doesn't count the ones with 10101***** for example. What do I do wrong?

    Thanks a lot and I hope you can help me with this last problem.

    Dijkhouse

  9. #9
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Make sure the cell you enter the code into is formatted as TEXT before you make an entry.

    You should see all the leading 0's.


  10. #10
    Quote Originally Posted by NBVC View Post
    Make sure the cell you enter the code into is formatted as TEXT before you make an entry.

    You should see all the leading 0's.
    Yes I formatted all the cells as text and I see all the 0's. But when I want to calculate how many people go from event 6 to 8, I use the formula Count.if(reach,00000101*). But with this formula, the ones with 10000101* for example are forgotten. On the other hand, if I use the formula Count.if(reach,*****101*) or Count.if(reach,*101*), The formula also counts people from event 2 to 4, or 4 to 6 instead of only the ones from 6 to 8. Is there a way to fix this?

Page 1 of 2 1 2 LastLast

Posting Permissions

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