Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting rows that meet multiple criteria
I have a strength report that generates counts based on multiple criteria.
The workbook has 2 sheets (Roster contains the personnel names and position numbers; Report contains the number crunching). The only criteria I cannot get to work is that only those rows that have data in the name field are included in the count. Column A has the position number and column B has the names. Not all positions are filled. I am only interested in counting rows of data that meet both criteria. How do I count rows that meet both criteria (correct number pattern and non-blank)? In the example below, I am interested in all employees in the 500 series who are full-time employees (indicated by the third digit being a "1"). This is my formula so far: =COUNTIFS(Roster!A:A,"=01?-??", Roster!B:B, XXXXXXX) A B C 1 521-01 Employee A NNNNNN 2 521-02 3 521-03 Employee B GGGGGG 4 521-04 5 522-01 Employee C VVVVVV 6 522-02 Employee D DDDDDD 7 522-03 8 522-04 9 531-01 Employee E KKKKKKK 10 531-02 11 531-03 12 532-01 Employee F LLLLLLLL 13 541-01 Employee G RRRRRR 14 541-02 15 541-03 Employee H YYYYYYY 16 542-01 Employee I EEEEEEE 17 542-02 Employee J WWWW 18 551-01 Employee K SSSSSS 19 551-02 20 551-03 Employee L FFFFFFF I'm ready to pull my hair out : / |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting rows that meet multiple criteria
An error in my original post. The formula I am using is:
=COUNTIFS(Roster!A:A,"=5?1-??", Roster!B:B, XXXXXXX) What is the syntax for the criteria that will count only rows with a name provided? "sabow71" wrote: I have a strength report that generates counts based on multiple criteria. The workbook has 2 sheets (Roster contains the personnel names and position numbers; Report contains the number crunching). The only criteria I cannot get to work is that only those rows that have data in the name field are included in the count. Column A has the position number and column B has the names. Not all positions are filled. I am only interested in counting rows of data that meet both criteria. How do I count rows that meet both criteria (correct number pattern and non-blank)? In the example below, I am interested in all employees in the 500 series who are full-time employees (indicated by the third digit being a "1"). This is my formula so far: =COUNTIFS(Roster!A:A,"=01?-??", Roster!B:B, XXXXXXX) A B C 1 521-01 Employee A NNNNNN 2 521-02 3 521-03 Employee B GGGGGG 4 521-04 5 522-01 Employee C VVVVVV 6 522-02 Employee D DDDDDD 7 522-03 8 522-04 9 531-01 Employee E KKKKKKK 10 531-02 11 531-03 12 532-01 Employee F LLLLLLLL 13 541-01 Employee G RRRRRR 14 541-02 15 541-03 Employee H YYYYYYY 16 542-01 Employee I EEEEEEE 17 542-02 Employee J WWWW 18 551-01 Employee K SSSSSS 19 551-02 20 551-03 Employee L FFFFFFF I'm ready to pull my hair out : / |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting rows that meet multiple criteria
How do I count rows that meet both criteria
(correct number pattern and non-blank)? Try it like this: =COUNTIFS(Roster!A:A,"??1*", Roster!B:B, "<") Assuming that blank cells in column B are *empty* cells. -- Biff Microsoft Excel MVP "sabow71" wrote in message ... I have a strength report that generates counts based on multiple criteria. The workbook has 2 sheets (Roster contains the personnel names and position numbers; Report contains the number crunching). The only criteria I cannot get to work is that only those rows that have data in the name field are included in the count. Column A has the position number and column B has the names. Not all positions are filled. I am only interested in counting rows of data that meet both criteria. How do I count rows that meet both criteria (correct number pattern and non-blank)? In the example below, I am interested in all employees in the 500 series who are full-time employees (indicated by the third digit being a "1"). This is my formula so far: =COUNTIFS(Roster!A:A,"=01?-??", Roster!B:B, XXXXXXX) A B C 1 521-01 Employee A NNNNNN 2 521-02 3 521-03 Employee B GGGGGG 4 521-04 5 522-01 Employee C VVVVVV 6 522-02 Employee D DDDDDD 7 522-03 8 522-04 9 531-01 Employee E KKKKKKK 10 531-02 11 531-03 12 532-01 Employee F LLLLLLLL 13 541-01 Employee G RRRRRR 14 541-02 15 541-03 Employee H YYYYYYY 16 542-01 Employee I EEEEEEE 17 542-02 Employee J WWWW 18 551-01 Employee K SSSSSS 19 551-02 20 551-03 Employee L FFFFFFF I'm ready to pull my hair out : / |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting rows that meet multiple criteria
Ooops!
employees in the 500 series I missed that part! Try this: =COUNTIFS(Roster!A:A,"5?1*", Roster!B:B, "<") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... How do I count rows that meet both criteria (correct number pattern and non-blank)? Try it like this: =COUNTIFS(Roster!A:A,"??1*", Roster!B:B, "<") Assuming that blank cells in column B are *empty* cells. -- Biff Microsoft Excel MVP "sabow71" wrote in message ... I have a strength report that generates counts based on multiple criteria. The workbook has 2 sheets (Roster contains the personnel names and position numbers; Report contains the number crunching). The only criteria I cannot get to work is that only those rows that have data in the name field are included in the count. Column A has the position number and column B has the names. Not all positions are filled. I am only interested in counting rows of data that meet both criteria. How do I count rows that meet both criteria (correct number pattern and non-blank)? In the example below, I am interested in all employees in the 500 series who are full-time employees (indicated by the third digit being a "1"). This is my formula so far: =COUNTIFS(Roster!A:A,"=01?-??", Roster!B:B, XXXXXXX) A B C 1 521-01 Employee A NNNNNN 2 521-02 3 521-03 Employee B GGGGGG 4 521-04 5 522-01 Employee C VVVVVV 6 522-02 Employee D DDDDDD 7 522-03 8 522-04 9 531-01 Employee E KKKKKKK 10 531-02 11 531-03 12 532-01 Employee F LLLLLLLL 13 541-01 Employee G RRRRRR 14 541-02 15 541-03 Employee H YYYYYYY 16 542-01 Employee I EEEEEEE 17 542-02 Employee J WWWW 18 551-01 Employee K SSSSSS 19 551-02 20 551-03 Employee L FFFFFFF I'm ready to pull my hair out : / |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting rows that meet multiple criteria
OMG!!!!! "<" That's it?!!!!
Thank you very much. "T. Valko" wrote: Ooops! employees in the 500 series I missed that part! Try this: =COUNTIFS(Roster!A:A,"5?1*", Roster!B:B, "<") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... How do I count rows that meet both criteria (correct number pattern and non-blank)? Try it like this: =COUNTIFS(Roster!A:A,"??1*", Roster!B:B, "<") Assuming that blank cells in column B are *empty* cells. -- Biff Microsoft Excel MVP "sabow71" wrote in message ... I have a strength report that generates counts based on multiple criteria. The workbook has 2 sheets (Roster contains the personnel names and position numbers; Report contains the number crunching). The only criteria I cannot get to work is that only those rows that have data in the name field are included in the count. Column A has the position number and column B has the names. Not all positions are filled. I am only interested in counting rows of data that meet both criteria. How do I count rows that meet both criteria (correct number pattern and non-blank)? In the example below, I am interested in all employees in the 500 series who are full-time employees (indicated by the third digit being a "1"). This is my formula so far: =COUNTIFS(Roster!A:A,"=01?-??", Roster!B:B, XXXXXXX) A B C 1 521-01 Employee A NNNNNN 2 521-02 3 521-03 Employee B GGGGGG 4 521-04 5 522-01 Employee C VVVVVV 6 522-02 Employee D DDDDDD 7 522-03 8 522-04 9 531-01 Employee E KKKKKKK 10 531-02 11 531-03 12 532-01 Employee F LLLLLLLL 13 541-01 Employee G RRRRRR 14 541-02 15 541-03 Employee H YYYYYYY 16 542-01 Employee I EEEEEEE 17 542-02 Employee J WWWW 18 551-01 Employee K SSSSSS 19 551-02 20 551-03 Employee L FFFFFFF I'm ready to pull my hair out : / |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting rows that meet multiple criteria
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "sabow71" wrote in message ... OMG!!!!! "<" That's it?!!!! Thank you very much. "T. Valko" wrote: Ooops! employees in the 500 series I missed that part! Try this: =COUNTIFS(Roster!A:A,"5?1*", Roster!B:B, "<") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... How do I count rows that meet both criteria (correct number pattern and non-blank)? Try it like this: =COUNTIFS(Roster!A:A,"??1*", Roster!B:B, "<") Assuming that blank cells in column B are *empty* cells. -- Biff Microsoft Excel MVP "sabow71" wrote in message ... I have a strength report that generates counts based on multiple criteria. The workbook has 2 sheets (Roster contains the personnel names and position numbers; Report contains the number crunching). The only criteria I cannot get to work is that only those rows that have data in the name field are included in the count. Column A has the position number and column B has the names. Not all positions are filled. I am only interested in counting rows of data that meet both criteria. How do I count rows that meet both criteria (correct number pattern and non-blank)? In the example below, I am interested in all employees in the 500 series who are full-time employees (indicated by the third digit being a "1"). This is my formula so far: =COUNTIFS(Roster!A:A,"=01?-??", Roster!B:B, XXXXXXX) A B C 1 521-01 Employee A NNNNNN 2 521-02 3 521-03 Employee B GGGGGG 4 521-04 5 522-01 Employee C VVVVVV 6 522-02 Employee D DDDDDD 7 522-03 8 522-04 9 531-01 Employee E KKKKKKK 10 531-02 11 531-03 12 532-01 Employee F LLLLLLLL 13 541-01 Employee G RRRRRR 14 541-02 15 541-03 Employee H YYYYYYY 16 542-01 Employee I EEEEEEE 17 542-02 Employee J WWWW 18 551-01 Employee K SSSSSS 19 551-02 20 551-03 Employee L FFFFFFF I'm ready to pull my hair out : / |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting rows that meet conditions in multiple columns | Excel Worksheet Functions | |||
Counting characters in multiple rows when rows meet specific criteria | Excel Worksheet Functions | |||
Counting # of cells with that meet criteria in two columns | Excel Worksheet Functions | |||
Counting Consecutive Cells that meet Criteria | Excel Discussion (Misc queries) | |||
Counting lines that meet TWO criteria | Excel Worksheet Functions |