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.

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

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" <(E-Mail Removed)> wrote in message
news:0BE3EE62-4AE9-41D7-91D5-(E-Mail Removed)...
> 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.

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]

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" <(E-Mail Removed)> wrote in message
> news:0BE3EE62-4AE9-41D7-91D5-(E-Mail Removed)...
> > 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.
>
>