Counting data in rows.

  • Thread starter Thread starter paulrm906
  • Start date Start date
P

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
 
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)
 
:) 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)
 
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
 
paulrm906 said:
.. 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) ..
Click to expand...

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

---
 
Back
Top