Counts based on rolling time periods

[jclaibor]

Hi everyone

I have a dataset containing purchase data.
A B C
Date/Time Card# Amount
1 1/3/08 12:00 x1xxx 15.00
2 1/3/08 12:21 x1xxx 25.00
3 1/3/08 12:55 xxxx3 55.00

I need to calculate the count and Amount sum for each card based on a
rolling 24 hours before and after the time of purchase. For instance if a
card was use on 1/3/08 12:00 AM I need to know how many times the card was
used between 1/2/08 12:00 AM and 1/4/08 12:00 AM and the total amount
purchased. If detail to the minute is overkill then I need at least to the
hour.

Thanks for any help

 

[Bob Phillips]

=SUMIF(A:A,">="&A2,C:C)-SUMIF(A:A,">="&A2+1,C:C)

 

[jclaibor]

Thanks Bob!

This certainly gives the date based sums. How does this address Column B?
Maybe I didn't explain it well. Column B is the credit card number and I
need to know anytime a credit card is used more than "x" times in any 24 hour
period or exceeds "x" dollars. Is there a practical way to calculate this?

 

[jclaibor]

Hi Bob

This didn't exactly do what I needed but I looked at your website and
was able to work it out with a SUMPRODUCT formula.

Thanks again