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)

 

[Max]

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

---