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

  • Thread starter Thread starter Hank01061567
  • Start date Start date
H

Hank01061567

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.
 
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
 
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))
 
What the heck...
=SUMPRODUCT(----(G1:G10="product"),--(MONTH(P1:P10)=n))
Click to expand...

Should be:
=SUMPRODUCT(--(G1:G10="product"),--(MONTH(P1:P10)=n))
=SUMPRODUCT(----(G1:G10=A1),--(MONTH(P1:P10)=B1))
Click to expand...

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