Counting days and text in cells

[Rocketeer]

I'm trying to create statistics from a sheet with order data from my webshop.
Each row consists of an order with:

Column A = order number
Column B = Payment option
Column C = Order date

Example:

1 PayPal 010110
2 VISA 010110
3 PayPal 020110
4 PayPal 020110
5 PayPal 020110
6 AMEX 030110


I'm interested to count how many orders have been placed each day during a
month and display an average based on the dates in column C. That is, 1st of
January = 2, 2nd of January = 3 etc. So for the three days above the average
number of orders/day = (2+3+1)/3 = 2.

I'm also interested to see the maximum number of orders for one day during a
month. That is, for the three day example above the result will be 3.

Best

 

[Per Jessen]

Hi

With a list of dates in E1:E3, use a helper column (column F in this
example) to count number of orders each day using:

=COUNTIF($C$1:C6;E1)

Calculate average using this - insert the formula in E1 and copy it
down as required:

=AVERAGE($F$1:F1)

To calculate max number of orders in one day use this:

=MAX(F1:F3)

Hopes this helps.
.....
Per

 

[Rocketeer]

Thanks Per!

I have my list of dates in column C, (C:C). I had a circular reference with
your example. If I use the following formula:

=COUNTIF(C:C;C1)

for each row and my helper column D in this case the example will be:

A B C D
---------------------------
1 PayPal 010110 2
2 VISA 010110 2
3 PayPal 020110 3
4 PayPal 020110 3
5 PayPal 020110 3
6 AMEX 030110 1

But I would prefere this instead:

A B C D
---------------------------
1 PayPal 010110
2 VISA 010110 2
3 PayPal 020110
4 PayPal 020110
5 PayPal 020110 3
6 AMEX 030110 1

or

A B C D
---------------------------
1 PayPal 010110 2
2 VISA 010110
3 PayPal 020110 3
4 PayPal 020110
5 PayPal 020110
6 AMEX 030110 1

Otherwise the average calculation is wrong.

That is, I only want to display the total number of orders for each day once
at the last/first order row in each date group.

Any help appriciated

Best

 

[Rocketeer]

Hi!

It works with the following formula in column D beginning on row 2:

=IF(COUNTIF(C:C;C2)=COUNTIF(C:C;C1);"";COUNTIF(C:C;C2))

Then the example will be:

A B C D
---------------------------
1 PayPal 010110 2
2 VISA 010110
3 PayPal 020110 3
4 PayPal 020110
5 PayPal 020110
6 AMEX 030110 1

Thanks for the inspiration