At 03:34 23/10/2008 +0000, Girard Aquino wrote:
Hi! I have a data sheet that has names, dates they attended and
whether or not they did attend. i would like to see how many people
actually attended for the first quarter, and/or by month. So for
example, i would have 15 names listed and scheduled for the month of
feb 2008, but only 10 of them actually attended. i have the dates
they should attend on column b, and have added another column c for
status/comments (this is where i place either attended, confirmed or
). i got the total number of people who attended using
countif(xxx;"=attended"). any suggestions of how to do this?
The problem here is not so much how to combine two criteria, of
course, but that COUNTIF() appears only to be able to examine a
single vector (e.g. column) of values. I don't see any way in which
you can get COUNTIF() to examine the date and attendance columns in a
single formula. So a solution is to combine these values into a
single parameter, which you can then count with COUNTIF().
For example, if you wanted totals for months you could copy
=MONTH(B1)&YEAR(B1)&C1
down a new column. You would then get values such as "22008attended"
for someone who attended in February 2008 and perhaps
"42008confirmed" for someone who failed to attend in April 2008.
Alternatively,
=INT((MONTH(B1)-1)/3+1)&YEAR(B1)&C1
will give you values such as "12008attended" for someone who attended
in the first quarter of 2008 and "22008confirmed" for someone who
failed to attend in the second quarter of 2008.
You can then evaluate the relevant totals by applying COUNTIF() to
the new column. If you prefer, you can hide this extra column, of course.
It is also possible to use the so-called database function DCOUNT()
to do this sort of thing. (That's a "database" function in a Calc
spreadsheet, not to be confused with a Base database.) This allows
you to apply conditions to more than one column simultaneously. It
works, but I don't think it makes the solution any easier.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
