Counting data in rows.

Hello this is a formular that I have been trying to workout now for some time, what I have is a spread sheet (Which I will call "Database") with staff Name in colmn A, ID Numbers in colmn B and their attendance records from colmn C to colmn IV (last colmn) going across ways. Now what I would like to be able to do is have seperate page (which I will name "Database records") where I can record all monthly/yearly records so what I need is a formular to to match their ID on the sheet Database and then for example count across ways how many sick days (SC) they had in Jan 05. I am of cause not even too sure if this formular is even possible.

Thankyou in advance if anyone can help me.

Paul Maynard

Assuming that row 1 has dates that can be test, this will get the total for
whoever is in row 2. I would also insert a column in C for these totals

=SUMPRODUCT(--(MONTH($D1:$IV1)=8),--($D2:$IV2="SC"))



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"paulrm906" <[email protected]> wrote
in message news:[email protected]...
>
> Hello this is a formular that I have been trying to workout now for some
> time, what I have is a spread sheet (Which I will call "Database") with
> staff Name in colmn A, ID Numbers in colmn B and their attendance
> records from colmn C to colmn IV (last colmn) going across ways. Now
> what I would like to be able to do is have seperate page (which I will
> name "Database records") where I can record all monthly/yearly records
> so what I need is a formular to to match their ID on the sheet Database
> and then for example count across ways how many sick days (SC) they had
> in Jan 05. I am of cause not even too sure if this formular is even
> possible.
>
> Thankyou in advance if anyone can help me.
>
> Paul Maynard
>
>
> --
> paulrm906
> ------------------------------------------------------------------------
> paulrm906's Profile:
http://www.excelforum.com/member.php...o&userid=28776
> View this thread: http://www.excelforum.com/showthread...hreadid=500548
>

The formular for me is not exactly what I was hoping for but the formular that you did give to me I have already put it to good use and it is also very good. This formular would be excellant if Microsoft Excel would allow a much larger number of colmns across, but I think we both now this will not happen in a very long time. But what I was really looking for was something simular too =INDEX('Staff Details'!$B$2:$P$323;MATCH($B$2;'Staff Details'!$C$2:$C$243;0);7) but rather then look up one colmn it could look up a range of colmns but it would have to match $B$2 (thier ID number).

And thanks very much for your speady responce.

Paul Maynard

Moscow.

Actually, you are wrong. The next release of Office, Office 12, will have
16K columns, as well as 1M+ rows.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"paulrm906" <[email protected]> wrote
in message news:[email protected]...
>
> The formular for me is not exactly what I was hoping for but the
> formular that you did give to me I have already put it to good use and
> it is also very good. This formular would be excellant if Microsoft
> Excel would allow a much larger number of colmns across, but I think we
> both now this will not happen in a very long time. But what I was really
> looking for was something simular too =INDEX('Staff
> Details'!$B$2:$P$323;MATCH($B$2;'Staff Details'!$C$2:$C$243;0);7) but
> rather then look up one colmn it could look up a range of colmns but it
> would have to match $B$2 (thier ID number).
>
> And thanks very much for your speady responce.
>
> Paul Maynard
>
> Moscow.
>
>
> --
> paulrm906
> ------------------------------------------------------------------------
> paulrm906's Profile:
http://www.excelforum.com/member.php...o&userid=28776
> View this thread: http://www.excelforum.com/showthread...hreadid=500548
>

Hello Bob

If you are correct about the colmns and rows in the next release from Microsoft Office then that is great news and I can not wait to get an update version.

Paul Maynard

Originally Posted by Bob Phillips

Actually, you are wrong. The next release of Office, Office 12, will have
16K columns, as well as 1M+ rows.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"paulrm906" <[email protected]> wrote
in message news:[email protected]...
>
> The formular for me is not exactly what I was hoping for but the
> formular that you did give to me I have already put it to good use and
> it is also very good. This formular would be excellant if Microsoft
> Excel would allow a much larger number of colmns across, but I think we
> both now this will not happen in a very long time. But what I was really
> looking for was something simular too =INDEX('Staff
> Details'!$B$2:$P$323;MATCH($B$2;'Staff Details'!$C$2:$C$243;0);7) but
> rather then look up one colmn it could look up a range of colmns but it
> would have to match $B$2 (thier ID number).
>
> And thanks very much for your speady responce.
>
> Paul Maynard
>
> Moscow.
>
>
> --
> paulrm906
> ------------------------------------------------------------------------
> paulrm906's Profile:
http://www.excelforum.com/member.php...o&userid=28776
> View this thread: http://www.excelforum.com/showthread...hreadid=500548
>

Hi Paul,

It's all public information. You can read about it at
http://blogs.msdn.com/excel/archive/2005/9/26.aspx

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"paulrm906" <[email protected]> wrote in
message news:[email protected]...
>
> Hello Bob
>
> If you are correct about the colmns and rows in the next release from
> Microsoft Office then that is great news and I can not wait to get an
> update version.
>
> Paul Maynard
>
> Bob Phillips Wrote:
> > Actually, you are wrong. The next release of Office, Office 12, will
> > have
> > 16K columns, as well as 1M+ rows.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "paulrm906" <[email protected]>
> > wrote
> > in message
> > news:[email protected]...
> > >
> > > The formular for me is not exactly what I was hoping for but the
> > > formular that you did give to me I have already put it to good use
> > and
> > > it is also very good. This formular would be excellant if Microsoft
> > > Excel would allow a much larger number of colmns across, but I think
> > we
> > > both now this will not happen in a very long time. But what I was
> > really
> > > looking for was something simular too =INDEX('Staff
> > > Details'!$B$2:$P$323;MATCH($B$2;'Staff Details'!$C$2:$C$243;0);7)
> > but
> > > rather then look up one colmn it could look up a range of colmns but
> > it
> > > would have to match $B$2 (thier ID number).
> > >
> > > And thanks very much for your speady responce.
> > >
> > > Paul Maynard
> > >
> > > Moscow.
> > >
> > >
> > > --
> > > paulrm906
> > >
> > ------------------------------------------------------------------------
> > > paulrm906's Profile:
> > http://www.excelforum.com/member.php...o&userid=28776
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=500548
> > >
>
>
> --
> paulrm906
> ------------------------------------------------------------------------
> paulrm906's Profile:
http://www.excelforum.com/member.php...o&userid=28776
> View this thread: http://www.excelforum.com/showthread...hreadid=500548
>

"paulrm906" wrote:
> .. But what I was really looking for was something similar to
> =INDEX('Staff Details'!$B$2:$P$323;
> MATCH($B$2;'Staff Details'!$C$2:$C$243;0);7) but
> rather then look up one column it could look up a range of columns
> but it would have to match $B$2 (their ID number) ..

Inferring your set-up from your posted formula above,
think we could try either of the formulas* below (normal ENTER):
(lightly tested, both seems to work ok)


=INDEX(OFFSET('Staff Details'!$A:$A,,
MATCH($B2,'Staff Details'!$2:$2,0)-1),
MATCH($B2,'Staff Details'!$C:$C,0))

(above formula attempts to use "simpler" entire col/row references)

=INDEX('Staff Details'!$C$2:$P$243,
MATCH($B2,'Staff Details'!$C$2:$C$243,0),
MATCH($B2,'Staff Details'!$C$2:$P$2,0))

(above formula conforms closely to the ranges in your posted formula, except
that I've adjusted the row ref in the indexed range: 'Staff
Details'!$C$2:$P$243, to sync with the "243" limit for the row match: 'Staff
Details'!$C$2:$C$243. We could have it the other way round, of course.)

*You'd need to change the commas to semicolons
in the formulas above to suit your continental Excel version
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---