CountIf on not number values. How can I solve?

[Nicola M]

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
structure:
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.

 

[Mike H]

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

 

[Rick Rothstein]

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.

 

[Nicola M]

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.

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]

 

[Nicola M]

Thank you Rick.

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.