Counting data in rows.

[paulrm906]

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

 

[Bob Phillips]

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]

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.

 

[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]

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]

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)