Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf on not number values. How can I solve?
Hi all.
Excel 2003. I need to count not number values using criteria. In the cell to count I have letters (like A, L, R etc stands for Absent, Leave, Rest etc). THis count is to be for nation and I have nationalities in unsorted order in an other cell. I can't use CountIf because cells don't contain number thought the nation criteria is respected with this function. Using Count or COuntA I count correctly the values but without the nation criteria. Even Using VBA is there a way to solve this issue? My sheet headers are on row 1. Data starts from Row 2 with the following structu A= Surname and Name; B= Nationality; C to n= Days number Thanks in advance for every kind of help or suggestion and for my non perfect English. Nicola M. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf on not number values. How can I solve?
Hi Nicola,
I don't understand your data layout. You refer to A= Surname and Name; B= Nationality; C to n= Days number but don't say where these are letters (like A, L, R etc stands for Absent, Leave, Rest etc). This may point you in the right direction To count English absence =SUMPRODUCT((B1:B20="English")*(C1:C20)) To count Nicola's absence =SUMPRODUCT((A1:A20="Nicola M")*(B1:B20="English")*(C1:C20)) Mike "Nicola M" wrote: Hi all. Excel 2003. I need to count not number values using criteria. In the cell to count I have THis count is to be for nation and I have nationalities in unsorted order in an other cell. I can't use CountIf because cells don't contain number thought the nation criteria is respected with this function. Using Count or COuntA I count correctly the values but without the nation criteria. Even Using VBA is there a way to solve this issue? My sheet headers are on row 1. Data starts from Row 2 with the following structu A= Surname and Name; B= Nationality; C to n= Days number Thanks in advance for every kind of help or suggestion and for my non perfect English. Nicola M. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf on not number values. How can I solve?
If I understand your layout correctly, you can do something like this to
count all of the, say, American Leaves... =SUMPRODUCT((B2:B200="American")*(C2:IV200="L")) where the 200s need to be a number large enough to cover the maximum row number you ever expect to have data in. You can also adjust the IV column reference to the actual maximum column you ever expect to have data in. To get any other statistics, just change the "American" and the "L" to what you are looking for. -- Rick (MVP - Excel) "Nicola M" wrote in message ... Hi all. Excel 2003. I need to count not number values using criteria. In the cell to count I have letters (like A, L, R etc stands for Absent, Leave, Rest etc). THis count is to be for nation and I have nationalities in unsorted order in an other cell. I can't use CountIf because cells don't contain number thought the nation criteria is respected with this function. Using Count or COuntA I count correctly the values but without the nation criteria. Even Using VBA is there a way to solve this issue? My sheet headers are on row 1. Data starts from Row 2 with the following structu A= Surname and Name; B= Nationality; C to n= Days number Thanks in advance for every kind of help or suggestion and for my non perfect English. Nicola M. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf on not number values. How can I solve?
Really I didn't know this formula. It's just that I need.
Sorry for the bad explanation of layout. C is 1st of the month, D is 2nd and so on. Letters could be from C2" to lastRow,LastCol and I need to get daily report of L,R and A divided by nation. Thank you again. Nicola M. "Mike H" wrote: Hi Nicola, I don't understand your data layout. You refer to A= Surname and Name; B= Nationality; C to n= Days number but don't say where these are letters (like A, L, R etc stands for Absent, Leave, Rest etc). [CUT] |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf on not number values. How can I solve?
Thank you Rick.
"Rick Rothstein" wrote: If I understand your layout correctly, you can do something like this to count all of the, say, American Leaves... =SUMPRODUCT((B2:B200="American")*(C2:IV200="L")) where the 200s need to be a number large enough to cover the maximum row number you ever expect to have data in. You can also adjust the IV column reference to the actual maximum column you ever expect to have data in. To get any other statistics, just change the "American" and the "L" to what you are looking for. -- Rick (MVP - Excel) "Nicola M" wrote in message ... Hi all. Excel 2003. I need to count not number values using criteria. In the cell to count I have letters (like A, L, R etc stands for Absent, Leave, Rest etc). THis count is to be for nation and I have nationalities in unsorted order in an other cell. I can't use CountIf because cells don't contain number thought the nation criteria is respected with this function. Using Count or COuntA I count correctly the values but without the nation criteria. Even Using VBA is there a way to solve this issue? My sheet headers are on row 1. Data starts from Row 2 with the following structu A= Surname and Name; B= Nationality; C to n= Days number Thanks in advance for every kind of help or suggestion and for my non perfect English. Nicola M. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Solve 1st of month after specified number of days elapsed | Excel Worksheet Functions | |||
how to solve this by countif | Excel Worksheet Functions | |||
excel doest not shows the number starting with 0..is there is any way to solve this. | Excel Discussion (Misc queries) | |||
countif formula to find the occurances of a number that is greater than one number but less than another | Excel Discussion (Misc queries) | |||
Countif between two values | Excel Worksheet Functions |