Counts based on rolling time periods
 

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

Counts based on rolling time periods
 

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

--
__________________________________
HTH

Bob

"jclaibor" wrote in message
...
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

Counts based on rolling time periods
 

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?

"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

"jclaibor" wrote in message
...
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

Counts based on rolling time periods
 

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

"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

"jclaibor" wrote in message
...
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