Formula For Individuals Attending Events

[Hyperdog882]

Hi all. I have a spreadsheet for events that our church hosts throughout the year. I have our individual ladies' names in it and them create a new column for each event and record a 1 in that column if they attend. Each column totals how many attend each event and each row totals how many events each have attended.
I am looking for a formula that will total how many unique individuals have attended all events thoughout the year.
Example:

Name. Event 1. Event 2. Event 3. Total
Ann Smith. 1. 1. 2
Jane Jones. 1. 1
Sally Martin 0
Beth Andrews. 1 1. 1. 1

Total. 2. 3. 1.

Need a formula that, in the above case, would tell me that 3 individuals have attended events this year.

[pjwhitfield]

Use COUNTIF to see how many have > 0 in the total column

Please Login or Register  to view this content.

[mrice]

Try

=COUNTIF(E2:E5,"<>0")

where the range is your totals.

[Hyperdog882]

Thanks pfwhitfield. I've tried that and it just gives me a 0. Not sure if the issue is the fact that the numbers in column I'm referencing are formula derived, i.e. - the actual value for each cell is a formula and not just a number?

[Hyperdog882]

Thanks mrice. That gives me the total number of names that I have but does not exclude 0s.

[pjwhitfield]

That shouldnt have any impact at all, your total column is in E isnt it? or youve changed the formula to the correct column?

Can you upload your workbook?

[pjwhitfield]

Thanks mrice. That gives me the total number of names that I have but does not exclude 0s.

Not sure whats happening there like as both mine and Mrice formulas were the same other than his used a smaller range. Both should have given same result for the data you supplied

[Hyperdog882]

Not sure whats happening there like as both mine and Mrice formulas were the same other than his used a smaller range. Both should have given same result for the data you supplied

Strange. I've attached my spreadsheet (Hopefully).

[FlameRetired]

Try this in R5 and fill / copy down.

Please Login or Register  to view this content.

Insert new columns to the right of column Q, enter the new data. The formula columns will move and the formula range will expand as you add new columns.

Edit Also in row 183 totals this formula in D183 copy and paste to the remaining cells to the right.

Please Login or Register  to view this content.

and for the COUNTIF formula

Please Login or Register  to view this content.

[pjwhitfield]

Try this in R5 and fill / copy down.

Please Login or Register  to view this content.

Insert new columns to the right of column Q, enter the new data. The formula columns will move and the formula range will expand as you add new columns.

Edit Also in row 183 totals this formula in D183 copy and paste to the remaining cells to the right.

Please Login or Register  to view this content.

and for the COUNTIF formula

Please Login or Register  to view this content.

Im with the OP here though, that still gives the incorrect answer of the full number of people rather than those that had attended

[pjwhitfield]

Theres something strange about your workbook, its definitely not calculating as it should.

Heres what should happen.

[FlameRetired]

Im with the OP here though, that still gives the incorrect answer of the full number of people rather than those that had attended

OK. I don't seem to be getting the concept. I've been solving the wrong problems.

[Hyperdog882]

Ok, well at least I know that it's not just me! Leave it to me to have a malfunctioning workbook! I guess I will just continue manually counting them unless someone else has any suggestions. Thanks for trying!

[FlameRetired]

I am looking for a formula that will total how many unique individuals have attended all events thoughout the year.

You have a COUNTIF formula in A186. Try replacing that with

Please Login or Register  to view this content.

The answer is 2.

Unless there exists a count of all events =7 and there is no name associated with it (i.e. blank cell in column A) the $A$4:$A$181,"<>" part is unnecessary.

Is this what you were looking for?

Edit An afterthought: On the question of "unique individuals" ... if there is a chance that an individual with 100% attendance is listed more than once perhaps this formula would be what you want.

Please Login or Register  to view this content.

[pjwhitfield]

FlameRetired - a simple COUNTIF should do it though, theres a total column that says how many times a user has attended an event (R in the sheet I believe), that column is a simple SUM(C1:P1) formula so it gets answers such as 2, 5, 0, 1 etc

Therefore to see how many people have attended events you should simply need to count the number of items in that column which are > 0

It shouldnt need anything more than that and the COUNTIF should easily work on a simple column such as that.

The issue lies with something else in the workbook

EDIT: Ive just opened it again and amended A186 to

Please Login or Register  to view this content.

and it worked


ps the answer is 136 not 135

[Hyperdog882]

FlameRetired - a simple COUNTIF should do it though, theres a total column that says how many times a user has attended an event (R in the sheet I believe), that column is a simple SUM(C1:P1) formula so it gets answers such as 2, 5, 0, 1 etc

Therefore to see how many people have attended events you should simply need to count the number of items in that column which are > 0

It shouldnt need anything more than that and the COUNTIF should easily work on a simple column such as that.

The issue lies with something else in the workbook

EDIT: Ive just opened it again and amended A186 to

Please Login or Register  to view this content.

and it worked


ps the answer is 136 not 135

Thanks again for working on this pjwhitfield. I literally just copied and pasted your formula onto my spreadsheet and it still gives me a 0. No clue why - I think this spreadsheet just hates me!
And I stand corrected on the 136.