Counting if issues

anzesi

New Member
Joined
Jan 7, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
Hi everyone,

I am having issues counting the number of Males/Females that were enrolled in a study during the month of March (3/1/20-3/31/20). I'm not sure why, but when I try to use the COUNTIFS formula to figure out the number of females that were enrolled in March (between 3/1/20 and 3/31/20), the solution is always 0, even though it is supposed to be 2.

This is the formula I am using:
=countifs('Main Tracking Sheet'!E$9:E$999,"F",'Main Tracking Sheet'!I$9:I$999, ">=3/1/20", 'Main Tracking Sheet'!I$9:I$999, "<=3/31/20")

"Main tracking sheet" is where all the data is coming from (this is not the complete excel spreadsheet, just the relevant info for the formula):

Column E is gender, I is enrollment dates.

Genderenroll date
FTRUE
2/19/20
FTRUE
2/25/20
FTRUE
2/26/20
MTRUE
2/26/20
FTRUE
2/27/20
FTRUE
2/28/20
MTRUE
2/28/20
FTRUE
3/3/2020
FTRUE
3/13/2020
FTRUE

Any help is greatly appreciated, thank you!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
There is a good chance that your dates are text and not real dates ( as they are left-aligned in your example)
To correct ,select your column containing dates Click - Data -Text to columns - Finish
If you did not apply manual alignment your dates should now be right-aligned and your formula should work
 
Upvote 0
There is a good chance that your dates are text and not real dates ( as they are left-aligned in your example)
To correct ,select your column containing dates Click - Data -Text to columns - Finish
If you did not apply manual alignment your dates should now be right-aligned and your formula should work

Thank you for your suggestion. These dates are right-aligned in the spreadsheet, and are read as dates. I'm guessing it was the was I copy/pasted the data. COUNTIF works if I want to know how many people we enrolled in May, or if I want to know how many females are enrolled. When I try to figure out how many females were enrolled between May 1st and 31st, the value returned is always 0, even though I know it is 2.

Here is a link to the spreadsheet: Enrollment test.xlsx
 

Attachments

  • 1610033028890.png
    1610033028890.png
    237.8 KB · Views: 9
Upvote 0
The problem is that your data is a mess & that none of the dates have anything in col E on the same row as the date.
 
Upvote 0
The problem is that your data is a mess & that none of the dates have anything in col E on the same row as the date.
Lol. I had to delete a bunch of stuff bc it had our participants personal info. I assure you the original spreadsheet is not as messy :)

Other than that, I put the gender in the same row as the dates and it worked! Thanks :)
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top