Thread: Counting Groups with Formula that meet a criteria

I was hoping for a solution with a single formula. Essentially I've got a pivot table where the columns represent a series of days and the rows represent actions that a person takes during the day. The values are the number of times an action is performed. I was hoping for a formula where I could determine the number of people that were active each day. My example was an attempt to simplify the problem to a single day. Does this make sense? I could provide a multi-day example if that would help.

Perhaps this will clarify the issue. Imagine you have people visiting a site at multiple times over a period of days. Each visit is recorded along with a user's activity for each day of the period. I'm hoping for a formula that would record the number of visitors each day recognizing that a visitor might be recorded with multiple activities for any given day. Here's how the data might look:

Name, Code, Day1, Day2, Day 3
Joe, A,0,0,3
Joe, B,1,0,0
Dave,A,0,0,1
Dave,C,0,1,1

In this example, the formula would calculate 1 visitor on Day 1 (Joe activity B), 1 visitor for day 2 (Dave activity C) and 2 visitors on day 3 (Joe Activity A which he performed 3 times and Dave Activity C).

Thanks in advance to anyone who might help. This is my first post and I apologize for any confusion.

That was Great and exactly what I was looking for. Thank you so much I was wresting with that for several days. This might need to be a new thread but is there a way to utilize that formula as part of a subtotal so that only the filtered results are included in the count?

Your solution worked great and I have successfully adapted it to my spreadsheet. The only problem left is that the sheet is driven from an access query which produces different data each day with the total number of rows varying. I can calculate the last Row and place it in a cell named ARows. Could I use an indirect or address reference in the formula you provided to incorporate this reference so that I wouldn't have to modify my formula each day? For example, assume ARows contains the calculated value 5. How could I use this calculated value instead of the constant 5 in the formula?

That works with the exception of one reference. In this formula ARows equals 229. Here are the two renditions. First the one that works:

=IF(AND(G9>0,G10>0),SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(G13,ROW($B$13:$B$229)-ROW($B$13),0)),IF(G$13:INDEX(G:G,ARows)>0,MATCH($B$13:INDEX($B:$B,ARows),$B$13:INDEX($B:$B,ARows),0) )),MATCH($B$13:INDEX($B:$B,ARows),$B$13:INDEX($B:$B,ARows),0)),1)),"")

To make it work I left the reference OFFSET(G13,ROWS($B$13:$B$229)-ROW($B$13)

If I change it as follows I get "The Formula contains and error" and the Index after the 13: is highlighted.
"=IF(AND(G9>0,G10>0),SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(G13,ROW($B$13:INDEX($B:$B,ARows))-ROW($B$13),0)),IF(G$13:INDEX(G:G,ARows)>0,MATCH($B$13:INDEX($B:$B,ARows),$B$13:INDEX($B:$B,ARows),0) )),MATCH($B$13:INDEX($B:$B,ARows),$B$13:INDEX($B:$B,ARows),0)),1)),"")

Any idea why this reference does not work?

You're right about too many threads for Excel 2003. That also explains why it worked when I tried each part on its own. Thanks so much for all your insight.