Hey everyone,
Hoping someone can help me.
Im trying to count the number of people in two different columns.
I dont really care who has each one, just how many.
For example how many people have both A & B or C & F
Data set is quite large 2500 columns by 1500 rows, so lots of pairs to count.
Each column has a header (name of item), each row has a name and if they have the item a 1 is placed in that column.
Any help would be great, thanks
Last edited by Croprock; 11-27-2019 at 10:28 PM.
not sure I follow what you want and what you posted.
for example, you have Dave in row 2 and a 1 under the header B and a 1 under the header D but in columns K and L you have A and B. Shouldn't it be B and D?
And what result do you want for Dave (and others)? You say you have 2500 columns, that is a wide workbook. Couldn't a simple sum be used at the end of the row that would show how many have a 2 or more showing they are in two or more columns?
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Hey,
The columns K & L were just examples, there are hundreds of pairs that I need to match.
I need to know how many people have both B & D, all figures are a 1 as no one has more than 1, so a sum function doesn't help.
Start with:
Please Login or Register to view this content.
Ben Van Johnson
a sum function would tell you if the person is in more than one column. So for Dave it would be 2 which, based so far on what you've written that sounds like what you want.
that is why I mentioned providing some examples of the output you expect.
Looks like Ben figured it out so that is good. It would be interesting to see that formula applied to 2500 columns but glad you (hopefully) got an answer.
Hi,
If you are looking to add how may times number of people are enrolled in BOTH A AND B try
Formula:
Please Login or Register to view this content.
Similarly, for C&F
Formula:Please Login or Register to view this content.
This is an array formula, Press Ctrl+Shift+Enter.
When you press Ctrl+Shift+Enter, Excel automatically inserts the formula between { } (a pair of opening and closing braces).
If you think someone helped you, click on the "* Add Reputation" as a way to say thank you.
If your problem is solved, go to Thread Tools and select Mark This Thread Solved
Tried =SUMPRODUCT(--(INDEX($B$2:$G$11,0,MATCH(K2,$B$1:$G$1,0))=1),--(INDEX($B$2:$G$11,0,MATCH(L2,$B$1:$G$1,0))=1))
Changed it to fit my workbook and boom town!
Ben, you are a legend!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks