On the Excel 2003 S.Sheet that I am working on, I have client start dates. I have made a table next to this, with individual cells for each month, going back 3 yrs. I wish to look at the start dates and extract how many people started in August 05 and then, having copied the formula down, queary the same data to show Sept 05, Oct 05 etc.
I have tried placing this as two tables in general format between two dates so that the number of days is displayed (Since 1//1/1900) and using a countif statement to measure between upper and lower values. No luck as, although accepting number inputs within the formula, doesn.t like locational references for some reason. Arghhh. Help.
2007-03-29 04:46:39 · 2 answers · asked by Alice S 6 in Computers & Internet ➔ Software
I think what you say will work
You might want to work from a Template before Making it specific for you; The Personnel Templates give some Great code, the ones at msoffice.com in the templates; Also this problem has come up countless time at mrexcel.com
2007-03-29 04:50:58 · answer #1 · answered by Mictlan_KISS 6 · 0⤊ 0⤋
Countif Between Dates
2016-10-01 05:45:35 · answer #2 · answered by Anonymous · 0⤊ 0⤋
Countif Between Two Values
2017-01-05 05:16:22 · answer #3 · answered by Anonymous · 0⤊ 0⤋
COUNTIF doesn't let you count occurences of data by telling the function to look between two values. I know, I've tried ... The little stinker only lets you enter one criterion! ARRGH indeed!
I did find a way to work around it ... it takes more work than just setting up COUNTIF functions, but it did the job. Also, my example involves people's ages, but I think you can adapt it to work with your client start dates.
In my situation, I had a list of people's ages in an Excel spreadsheet. I needed to find out how many people were in the age categories: under 18, 18-24, 25-34, 35-49, 50-64, and 65 and over. Let's say my data is in a long column, cells B1 to B1000, and at the bottom of the sheet, I'm putting in the COUNTIFs.
The first one to set up is obivous ... COUNTIF(B1:B1000,"<18"), let's put in in cell B1003. That gives me the number under 18.
The next COUNTIF (in B1004) has the same range, but the criteria is now "<=24." That gives me the total of the under 18's plus the 18-24 year olds.
Now here's the trick: In another cell (say,C1004), give it the formula =B1004-B1003. Now I have the number of 18-24-year-olds!
Next up: COUNTIF in B1005, "<=34." Formula in cell C1005, =B1005-B1004, to get the number in ages 25-34.
And so on. I just keep on working my way up through the age ranges until the last COUNTIF I put in is for ">=65."
It does take more work to set up, but once you've done a couple it's a snap. And like I said, I used this for counting age data by category, but I think you can adapt it to your start dates. Good luck!
2007-03-29 05:26:02 · answer #4 · answered by Navigator 7 · 1⤊ 0⤋
Your spreadsheet is not set up optimally.
It needs only 2 columns.
column a - client name
column b - start dates
Using pivottables, you can create a report showing the number of clients who joined at various dates.
The dates can be grouped by month, years, whatever that is meaningful for you.
2007-03-29 06:35:16 · answer #5 · answered by unnga 6 · 1⤊ 0⤋