I hope someone can help me here, a friend of mine and I are pulling our hair out. Here’s the situation (it’s more complicated than this but I’ve simplified it to not muddle the question)
We’ve got a spreadsheet with a list of names in the A column. The B and C columns are for various qualifications each person has - we place an X next to their names to identify who has what.
Now what we want to do is count how many people have X in BOTH columns. The COUNTIF function will do it for one column, but I can’t figure out how to get it to do what we need. The help file is not being very helpful. Can anybody offer any advice?
Run Countif on B1 and C1 looking for an x as in =countif (B1:C1"x"). If the person in column A has both, the result will be 2. If the person in A has just one, it will be 1. Copy the formula all the way down in the D column and just look for the 2’s.
Use a dcounta function with your range as the database, the A column name as your field (the 2nd argument), then setup your criteria showing both columns B and C with the X in them.
=dcounta(dbRange,“Name”,dbCrit)
where
dbRange = A1:C100 (or whatever)
dbCrit = F1:H3
F1 thru H1 are the field names matching A1 thru C1
G2 and H2 have X’s in them.
In column D, insert function counta(b1:c1); it’ll count the instances of x’s present. This is the simplest form of the other “count” suggestions I’ve seen above. (Note: I’m running XP, but I believe the same function exists in 2000 also.)
Or, instead of using X’s, use the number 1 - and then do sum(b1:c1).
Thanks to those suggestions so far. I think I may need to clarify my problem though.
johnson - this is certainly a workable option, and probably the easiest. But this spreadsheet actually has many, many rows of information and I would like a “cleaner” solution. I may resort to this if I have to, but I’d prefer an “all-encompassing” formula rather than this two-step process.
Turbo Dog - this would be a good option if I wasn’t dealing with over 100 names on this spreadsheet. I really need an automated method.
hardcore - umm, I’ll have to study this one more, as I’m not initially real clear as to what you mean.
Earthling - I don’t need the total number of X’s, I need the total number of people who have an X in both columns, while excluding those who have an X in only one column or the other.
ski, you know you can copy a formula down an entire column with one click right? Just highlight it and the formula will paste itself in each cell in sequence. You don’t have to paste to individual cells.
Then if you don’t want to actually count the number of twos, simply run another countif down column D looking for 2’s and you’ll get the total number.
Having little do do during lunch, I created a listing like you described with 200 rows, three columns. Running the two countifs took me 37 seconds. I don’t know if there is one formula that will do it all in one step but I’m looking.
I think hardcore has the best suggestion, though it can be difficult setting up a DCOUNTA function the first time you work with it. One you have it set up, it has the advantage that you can quickly change the criteria and get different counts without having to redo the formula. You may want spend some time looking at the Excel help since it gives a good example of DCOUNTA.
Well I must be missing something because what I proposed seems much more simple and straightforward to me. And you can make it even simpler if you use logic values (true and false) or one and zero or one and blank in columns B and C as then you can easily make column D =A AND B or =A*B
Yeah, your suggestion was pretty simple, sailor…wonder why it took so long for someone to come up with that?
Actually, I’ve never used the database functions before, but I had cause to use one today. It does take some time to figure out, but seems very worthwhile for a number of purposes. I do think this would be one…[sub]though the IF statement, then summing up is awfully simple…[/sub]
I agree that this is the simplest solution (particularly using 1 and 0 as the indicator) for the scenario in the OP. I do that kind of thing all the time. It sounded, though, like that scenario was an oversimplified version of the real spreadsheet. If you start adding in more columns and are dealing with thousands of rows, it can get pretty cumbersome, particularly if you want to change the criteria. The database functions are better for that type of situation, but they can be a pain to set up.
sailor, I used the method you and johnson suggested many times when I first began using spreadsheets, and it certainly works well. But as JeffB mentioned, it becomes unwieldy as you add more rows and therefore have to add more copies of the formula, plus all the additional formulae adds unnecessary weight to the spreadsheet. The database functions have the benefit of succinctness, in addition to the ease of which you can change your criteria. Furthermore, you can setup your database range to increase automatically as you add rows so that you don’t have to worry about that part of it. Another side benefit is the fact that it teaches you to think of the data in terms of a database, which is a good foundation for a future programmer.
I think the real point here is that ski should use whatever method he or she is most comfortable with, but it is always wise to try and understand other approaches to improve your abilities and learn something new.
