Hi everyone,
I'm stumped on tidying up a situation and am hoping for help. This is related to a research project of mine.
My database is tracking items owned by men and women. I used to enter the gender of an owner in Column C once per unique user, and then in Column D entered the user's gender for every item in the database. (See the pic below.)
Formerly what worked:
A colleague recommended I assign owners unique identification numbers in order to cut down on my use of two separate columns for gender, and said that if I did so I could delete the C column and use just the current D column in which every item is flagged with the owner's gender.
My challenge is that I can't figure out how to change the formulas. Here's what the Descriptions worksheet, columns A-D, looks like now that I've added User IDs:
I've figured out that I can now count the total number of people by counting unique IDs using this formula:
=SUM(--(FREQUENCY(Descriptions!B2:B999,Descriptions!B2:B999)>0))
But how do I revise the formulas I identified in A., B., and C., above? (Note that all of my calculations are happening on a different worksheet than the Descriptions worksheet, which is just for entering data.)
I'm stumped on tidying up a situation and am hoping for help. This is related to a research project of mine.
My database is tracking items owned by men and women. I used to enter the gender of an owner in Column C once per unique user, and then in Column D entered the user's gender for every item in the database. (See the pic below.)
Formerly what worked:
A. To count how many men or women were in the dataset, I used this formula: =COUNTIF(Descriptions!C2:C999,"M") [Where M and F identified male and female users.] This works because, even when a person owns multiple items, I only enter M once in the C column for the first item each person owns.
B. If I want to count how many items were owned by men, I used this formula: =(COUNTIF(Descriptions!D2:D999,"M")) (This works because the D column identifies the owner's gender for every item in the dataset.)
C. If I wanted to count how many times men or women use a particular subset of items, I use this formula: =SUMPRODUCT((Descriptions!D2:D999="M")*(Descriptions!T2:AD999="SUBSET_CHARACTERISTIC")) [Subset characteristics are always text values.]
B. If I want to count how many items were owned by men, I used this formula: =(COUNTIF(Descriptions!D2:D999,"M")) (This works because the D column identifies the owner's gender for every item in the dataset.)
C. If I wanted to count how many times men or women use a particular subset of items, I use this formula: =SUMPRODUCT((Descriptions!D2:D999="M")*(Descriptions!T2:AD999="SUBSET_CHARACTERISTIC")) [Subset characteristics are always text values.]
A colleague recommended I assign owners unique identification numbers in order to cut down on my use of two separate columns for gender, and said that if I did so I could delete the C column and use just the current D column in which every item is flagged with the owner's gender.
My challenge is that I can't figure out how to change the formulas. Here's what the Descriptions worksheet, columns A-D, looks like now that I've added User IDs:
![2uzcmsw.jpg](/board/proxy.php?image=http%3A%2F%2Fi67.tinypic.com%2F2uzcmsw.jpg&hash=18ca2506bd9a6264626e3cca71bbc7d5)
I've figured out that I can now count the total number of people by counting unique IDs using this formula:
=SUM(--(FREQUENCY(Descriptions!B2:B999,Descriptions!B2:B999)>0))
But how do I revise the formulas I identified in A., B., and C., above? (Note that all of my calculations are happening on a different worksheet than the Descriptions worksheet, which is just for entering data.)