If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|