Calculating text values with owner ID numbers

Merellia

New Member
Joined
Jun 25, 2016
Messages
9
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
Joined
Jun 25, 2016
Messages
9
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
Joined
Jun 25, 2016
Messages
9
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.
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top