# Thread: Count.ifs with a lot of conditions

1. ## Count.ifs with a lot of conditions

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.

Dijkhouse

2. 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).

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.

DijkhouseDijkhouse - Example for forum.xlsx

4. Can you please re-upload and show some expected results? I am trying to understand what you mean by "Possible arrows".

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. 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. 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.

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. 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. Originally Posted by NBVC
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 Last

#### Posting Permissions

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