How to do a conditional sum?

  • Thread starter Thread starter bcv
  • Start date Start date
B

bcv

Hi,

I have rows like this:

USER DATE TIME STATUS

NG 03/11/09 09:43:47 IN
LP 03/11/09 09:57:45 OUT
PJ 03/11/09 09:58:14 IN
LH 03/11/09 10:10:11 IN
NG 03/11/09 10:39:07 OUT
PJ 03/11/09 11:17:25 OUT


For any given date and time (or range of dates) I would like to know
how many users are IN.

Any ideas?
 
I put your data in A1:D6 but I pretended it extended to row 30
Starting in F1 I entered these four value
03/11/2009.....9:0003/11/2009.....10:05
Then I used this formula
=SUMPRODUCT(--(B1:B30>=F1),--(B1:B30<=F2),--(C1:C30>=G1),--(C1:C30<=G2),--(D1:D30="IN"))
to tell me how many were IN, in dates between F1 anf F2 (inclusive) and
times G1 ang g2 (inclusive)
My answer was 2

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
Debra Dalgleish
http://www.contextures.com/xlFunctions04.html#SumProduct
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


in message news:[email protected]...
 
Thanks very much for this. I will try it out.

Ideally I would want to ignore a users 2nd login otherwise they would
be counted twice. eg if they logged in, out, and in again - I would
only want to count IN once.
Does that make sense?
 
I assume your date/time is one field, and it is called [Date Time]. You
would not want to name fields [Date] or [Time] in Access.

If you want to know which users are IN, they are the ones that have an odd
number of records. This assumes the data is clean, and you would not have
two 'IN' records without at least one of them having a matching 'OUT' record.
To do this, write one query containing the [User] and the [Date Time]
field. Using the Totals, 'Group by' the User, and 'Count' the Time Field.
Save this query.

Then make a second query based on the first query containing the User field
and another field containing the following ([CountOfDate Time] is the
default name from the first query unless you provided an alternate name):

[CountOfDate Time]/2-Int([CountOfDate Time]/2)

Since there isn't a 'Mod' function in Access, this will return a 0 for
records with an even count (these are 'OUT') and 0.5 for users with an add
count ('IN'). To restrict your query to pull only those that are IN, put
criteria below this expression field as follows:

Between 0.4 And 0.6

For a simple count, use the Totals, and 'Group by' the user field and set
the second field as an 'Expression'. You don't need to show this second
field.

Hope that is what you are looking for.
 
Back
Top