# Calculating text values with owner ID numbers

#### Merellia

##### New Member
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. 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.]​

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.)

#### Merellia

##### New Member
I just realized that my question B is irrelevant because it counts the frequency of items by gender in column D (which will remain), and doesn't require counting by frequency of unique User ID#s.

Question A is still applicable. How, using the User ID column and the D column, can I how many unique user IDs are men, and how many women?

And then the revised Question C: How, using the User ID column and the D gender column, can I count the frequency with men, or women, own a subset of items? I would be updating the following formula, so I can delete the C (owner gender) column: =SUMPRODUCT((Descriptions!C2:C999="M")*(Descriptions!T2:AD999="SUBSET_CHARACTERISTIC"))

Last edited:

#### Merellia

##### New Member
For Question A: I just tracked down and adapted the following formula, which SEEMS to work--except it produces a fraction, and I'm not sure why. There are no fractional people in the database. Here it is: =SUMPRODUCT((D2:D999="M")/COUNTIF(B2:B999,B2:B999&"")) -- the results indicate that there are 131.5 men.

1,082,126
Messages
5,363,321
Members
400,725
Latest member
excelingtolearn

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...