sum the number of rows in a spreadsheet given 2 criteria???
 

Hi,

I have a monthly sales report and i am trying to determine how many
customers we have at the end of each month for the last few years so i can
attempt to display the growth of our penetration.

The spreadsheet has in column C the name of the client (each client is
allocated its own row), the date (month end) is in column P, the product type
is in column G.

so essentially what i want i want to do is sum the number of rows for each
month for each product.

Any help is greatly appreciated!

Thanks in advance.

sum the number of rows in a spreadsheet given 2 criteria???
 

Is this summary going into a different sheet? How is the table laid
out in the summary? What cells are you using to hold the months,
products etc?

Try to give an example of what you want to end up with.

Hope this helps.

Pete

On Nov 12, 10:05*pm, Hank01061567
wrote:
Hi,

I have a monthly sales report and i am trying to determine how many
customers we have at the end of each month for the last few years so i can
attempt to display the growth of our penetration.

The spreadsheet has in column C the name of the client (each client is
allocated its own row), the date (month end) is in column P, the product type
is in column G.

so essentially what i want i want to do is sum the number of rows for each
month for each product.

Any help is greatly appreciated!

Thanks in advance.

sum the number of rows in a spreadsheet given 2 criteria???
 

Maybe something like this...

=SUMPRODUCT(----(G1:G10="product"),--(MONTH(P1:P10)=n))

Where n = a month number from 1 to 12 (1 = Jan thru 12 = Dec)

Note that if a cell is empty it will evaluate as month number 1. To account
for empty cells:

=SUMPRODUCT(--(G1:G10="product"),--(P1:P10<""),--(MONTH(P1:P10)=n))

Better to use cells to hold the criteria...

A1 = some product
B1 = month number

=SUMPRODUCT(----(G1:G10=A1),--(MONTH(P1:P10)=B1))

=SUMPRODUCT(--(G1:G10=A1),--(P1:P10<""),--(MONTH(P1:P10)=B1))

--
Biff
Microsoft Excel MVP


"Hank01061567" wrote in message
...
Hi,

I have a monthly sales report and i am trying to determine how many
customers we have at the end of each month for the last few years so i can
attempt to display the growth of our penetration.

The spreadsheet has in column C the name of the client (each client is
allocated its own row), the date (month end) is in column P, the product
type
is in column G.

so essentially what i want i want to do is sum the number of rows for each
month for each product.

Any help is greatly appreciated!

Thanks in advance.

sum the number of rows in a spreadsheet given 2 criteria???
 

What the heck...

=SUMPRODUCT(----(G1:G10="product"),--(MONTH(P1:P10)=n))

Should be:
=SUMPRODUCT(--(G1:G10="product"),--(MONTH(P1:P10)=n))

=SUMPRODUCT(----(G1:G10=A1),--(MONTH(P1:P10)=B1))

Should be:
=SUMPRODUCT(--(G1:G10=A1),--(MONTH(P1:P10)=B1))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Maybe something like this...

=SUMPRODUCT(----(G1:G10="product"),--(MONTH(P1:P10)=n))

Where n = a month number from 1 to 12 (1 = Jan thru 12 = Dec)

Note that if a cell is empty it will evaluate as month number 1. To
account for empty cells:

=SUMPRODUCT(--(G1:G10="product"),--(P1:P10<""),--(MONTH(P1:P10)=n))

Better to use cells to hold the criteria...

A1 = some product
B1 = month number

=SUMPRODUCT(----(G1:G10=A1),--(MONTH(P1:P10)=B1))

=SUMPRODUCT(--(G1:G10=A1),--(P1:P10<""),--(MONTH(P1:P10)=B1))

--
Biff
Microsoft Excel MVP


"Hank01061567" wrote in message
...
Hi,

I have a monthly sales report and i am trying to determine how many
customers we have at the end of each month for the last few years so i
can
attempt to display the growth of our penetration.

The spreadsheet has in column C the name of the client (each client is
allocated its own row), the date (month end) is in column P, the product
type
is in column G.

so essentially what i want i want to do is sum the number of rows for
each
month for each product.

Any help is greatly appreciated!

Thanks in advance.