count in a sheet

[remigio]

Hi
in an excel's sheet I register customers of a shop in various records,
including name, surname, date of
birth, address, date of the first purchase, the closing date, paid
(where I write yes or not).
At the end of each month I have to count for the previous month how
many customers the shop has, inlcluding new customers, current adn
closed customers, if those customers are new entrants (based on date
of first purchase) and how many customers have closed the report.
Does it possible to do everything automatically, putting the results
in another sheet? (I think yes, but I am not able to do)
Thank you very much.

Remigio


_____________
www.sacraspina.it
www.amicitosondoro.it

 

[ShaneDevenshire]

Hi,

You should look at the COUNTIF(Range,Item) function. It will count the
number of occurances of an Item in a Range.

 

[remigio]

You should look at the COUNTIF(Range,Item) function. It will count the
number of occurances of an Item in a Range.

May you write me an example, please? I am not an expert user.
In particular I am not able to set counting based on date criterion,
and I can't decide how to set counting for new customers (based on
date of first purchase) and closed customers (date of last purchase)
Thank you very much.

Remigio

_____________
www.sacraspina.it
www.amicitosondoro.it

 

[ShaneDevenshire]

Hi,

Now you haven't told us what your data looks like in detail, but suppose
every a customer makes a purchase you log the date in the Date column. At
the end of the month, say Oct 1, but before you have added any new month's
data you could use the following:

=COUNTIF(E2:E2000,">=9/1/08")

This will cound every transaction in the month of September. If you want to
do this after you have already entered some October date you could use:

=COUNTIF(E2:E26,">9/1/08")-COUNTIF(E2:E26,">10/1/08")

both of these formula could me made more flexible by using range references.
Suppose I enter 9/1/08 in cell A1 and 10/1/08 in cell B1, then formula #1
and 2 become:

=COUNTIF(E2:E2000,">="&A1)

=COUNTIF(E2:E2000,">="&A1)-COUNTIF(E2:E2000,">="&B1)

 

[ShaneDevenshire]

Hi,

Continuing the last discussion - to get a count of new customers, assume
your unique customer names are in columm D and the date in column E then
create a new column, say in F and enter the formula
=COUNTIF(D$22,D2) in cell D2 and copy it down as far as your data.

Then label the top of column E "Count", I have titled the Name column Name
and the date column Date. In cells K,L and M1 I have entered the titles
Count, Date and Date. In cell K2 enter 1, in L2 I have entered >=9/1/2008,
in M1 <10/1/2008. In cell H1 I have entered the formula
=DCOUNTA(D1:F2000,D1,K1:L2)
This formula returns the count of the number of new customers in the month
of September 2008.

To make this more flexible you could enter 9/1/2008 in N2 and 10/1/2008 in
O2 and then change the entries in cell L2 to read
=">="&N2
and in M2
="<"&O2

With this second set of formulas you can change the date range of the count
by just entering the dates in N2 and O2.

 

[remigio]

Hi,

Continuing the last discussion - to get a count of new customers, assume
your unique customer names are in columm D and the date in column E then
create a new column, say in F and enter the formula
=COUNTIF(D$22,D2) in cell D2 and copy it down as far as your data.

Then label the top of column E "Count", I have titled the Name column Name
and the date column Date. In cells K,L and M1 I have entered the titles
Count, Date and Date. In cell K2 enter 1, in L2 I have entered >=9/1/2008,
in M1 <10/1/2008. In cell H1 I have entered the formula
=DCOUNTA(D1:F2000,D1,K1:L2)
This formula returns the count of the number of new customers in the month
of September 2008.

To make this more flexible you could enter 9/1/2008 in N2 and 10/1/2008 in
O2 and then change the entries in cell L2 to read
=">="&N2
and in M2
="<"&O2

With this second set of formulas you can change the date range of the count
by just entering the dates in N2 and O2.

Thank you very much.

 

[remigio]

Hi,

Now you haven't told us what your data looks like in detail, but suppose
every a customer makes a purchase you log the date in the Date column. At
the end of the month, say Oct 1, but before you have added any new month's
data you could use the following:

=COUNTIF(E2:E2000,">=9/1/08")

This will cound every transaction in the month of September. If you want to
do this after you have already entered some October date you could use:

=COUNTIF(E2:E26,">9/1/08")-COUNTIF(E2:E26,">10/1/08")

both of these formula could me made more flexible by using range references.
Suppose I enter 9/1/08 in cell A1 and 10/1/08 in cell B1, then formula #1
and 2 become:

=COUNTIF(E2:E2000,">="&A1)

=COUNTIF(E2:E2000,">="&A1)-COUNTIF(E2:E2000,">="&B1)

Thank you very much.

 

[remigio]

Hi
in an excel's sheet I register customers of a shop in various records,
including name, surname, date of
birth, address, date of the first purchase, the closing date, paid
(where I write yes or not).
At the end of each month I have to count for the previous month how
many customers the shop has, inlcluding new customers, current adn
closed customers, if those customers are new entrants (based on date
of first purchase) and how many customers have closed the report.
Does it possible to do everything automatically, putting the results
in another sheet? (I think yes, but I am not able to do)
Thank you very much.

Remigio

_____________www.sacraspina.itwww.amicitosondoro.it

..

 

[remigio]

Hi
in an excel's sheet I register customers of a shop in various records,
including name, surname, date of
birth, address, date of the first purchase, the closing date, paid
(where I write yes or not).
At the end of each month I have to count for the previous month how
many customers the shop has, inlcluding new customers, current adn
closed customers, if those customers are new entrants (based on date
of first purchase) and how many customers have closed the report.
Does it possible to do everything automatically, putting the results
in another sheet? (I think yes, but I am not able to do)
Thank you very much.

Remigio

_____________www.sacraspina.itwww.amicitosondoro.it

..