Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem using multiple criteria to get a count
Spreadsheet A has a tab for every month of the year. Each tab has a column
for meeting date and several columns for initials. The date column does not have a date in every row, so out of 20 or so rows, only two or three cells in the date column will be filled in. The same for the initials; i.e. just because a date is filled in, there may not be corresponding initials. I created a second spreadsheet with a single tab and linked the cells to those in spreadsheet A. Because there is not a date in every row, my results look like this: A B C Date Div1 Div2 1 01/15/08 CC 0 2 01/00/00 0 0 3 01/00/00 0 0 4 02/25/08 0 JM 5 03/03/08 MS MW 6 01/00/00 0 0 etc. What I want: I need to use a rolling date range, so that the spreadsheet looks at column A and pulls all dates between Today()-90 and Today() and gives me a count of how many of those rows have initials filled in for column B and how many of those rows have initials filled in for column C. I have tried using DCOUNTA with the following formula: =DCOUNTA(A6:B204,"CL",$A$2:$B$2), but I cannot get it to work. I can get it to work using the COUNTIF function such as follows: =COUNTIF(INDIRECT("'"&B$4&"'!A:A"),"="&$B$2)-COUNTIF(INDIRECT("'"&B$4&"'!A:A"),""&$B$2-90) But this just gives me a count for the rows of dates. Any help is appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem using multiple criteria to get a count
I see two things wrong
1) CL need to match the Header row of the column like Div1 and Div2 that are in row1 2) The third field in countA need to include the header Row. You want something like this =DCOUNTA(A6:B204,"Div1",$A$1:$B$2), click on the cell where DCountA is located and go to menu Insert - Function. Then click on "Help On this Function" in the lower left of the popup window. "CCripe" wrote: Spreadsheet A has a tab for every month of the year. Each tab has a column for meeting date and several columns for initials. The date column does not have a date in every row, so out of 20 or so rows, only two or three cells in the date column will be filled in. The same for the initials; i.e. just because a date is filled in, there may not be corresponding initials. I created a second spreadsheet with a single tab and linked the cells to those in spreadsheet A. Because there is not a date in every row, my results look like this: A B C Date Div1 Div2 1 01/15/08 CC 0 2 01/00/00 0 0 3 01/00/00 0 0 4 02/25/08 0 JM 5 03/03/08 MS MW 6 01/00/00 0 0 etc. What I want: I need to use a rolling date range, so that the spreadsheet looks at column A and pulls all dates between Today()-90 and Today() and gives me a count of how many of those rows have initials filled in for column B and how many of those rows have initials filled in for column C. I have tried using DCOUNTA with the following formula: =DCOUNTA(A6:B204,"CL",$A$2:$B$2), but I cannot get it to work. I can get it to work using the COUNTIF function such as follows: =COUNTIF(INDIRECT("'"&B$4&"'!A:A"),"="&$B$2)-COUNTIF(INDIRECT("'"&B$4&"'!A:A"),""&$B$2-90) But this just gives me a count for the rows of dates. Any help is appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count with multiple criteria in multiple cells | Excel Worksheet Functions | |||
Filter Multiple Columns on Multiple Criteria and count the last co | Excel Worksheet Functions | |||
To count the data using multiple criteria in multiple columns | New Users to Excel | |||
Count multiple cells against multiple criteria in an Excel spreads | Excel Worksheet Functions | |||
Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions |