|
Calculation of row count
I have two columns and five rows.
A Y 1 11.01.2005 1 12.06.2004 3 10.06.2005 4 15.03.2005 1 10.01.2005 Condition: A=1 and Y=2005 (this year) Requirement: to find the amount of rows with the help of some formula (= and so on) How is it possible to calculate that amount ? |
Calculation of row count
=Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005))
-- Regards, Tom Ogilvy "Alur" wrote in message ... I have two columns and five rows. A Y 1 11.01.2005 1 12.06.2004 3 10.06.2005 4 15.03.2005 1 10.01.2005 Condition: A=1 and Y=2005 (this year) Requirement: to find the amount of rows with the help of some formula (= and so on) How is it possible to calculate that amount ? |
Calculation of row count
in the immediate window of the vbe, put in a command like this
Range("AA1").Formula = "=Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005))" then go to the end of that command and hit enter to execute it. Look in cell AA1 and you should see the translated command. -- Regards, Tom Ogilvy "Alur" wrote in message ... Where can i find the same functions (like sumproduct) in other languages ? "Tom Ogilvy" wrote: =Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005)) -- Regards, Tom Ogilvy "Alur" wrote in message ... I have two columns and five rows. A Y 1 11.01.2005 1 12.06.2004 3 10.06.2005 4 15.03.2005 1 10.01.2005 Condition: A=1 and Y=2005 (this year) Requirement: to find the amount of rows with the help of some formula (= and so on) How is it possible to calculate that amount ? |
Calculation of row count
I used
Range("A7").Formula = "=Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005))" It translated and in the cell A7 the result is 0. Why? "Tom Ogilvy" wrote: in the immediate window of the vbe, put in a command like this Range("AA1").Formula = "=Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005))" then go to the end of that command and hit enter to execute it. Look in cell AA1 and you should see the translated command. -- Regards, Tom Ogilvy "Alur" wrote in message ... Where can i find the same functions (like sumproduct) in other languages ? "Tom Ogilvy" wrote: =Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005)) -- Regards, Tom Ogilvy "Alur" wrote in message ... I have two columns and five rows. A Y 1 11.01.2005 1 12.06.2004 3 10.06.2005 4 15.03.2005 1 10.01.2005 Condition: A=1 and Y=2005 (this year) Requirement: to find the amount of rows with the help of some formula (= and so on) How is it possible to calculate that amount ? |
Calculation of row count
My guess is that your dates:
1 11.01.2005 1 12.06.2004 3 10.06.2005 4 15.03.2005 1 10.01.2005 Are really text. If you try to format one of those dates (format|Cells|number tab|date category) to a different format, does the cell change? If yes, then I'm wrong. If no, you have text in those cells, not real dates. I'd fix the data. Select that range that contain your Text dates. data|text to columns. fixed width (and remove every line that excel guessed) choose dmy as the format of the date format the cell the way you want (custom as dd.mm.yyyy would give the same look, but the values would be real dates.) ======= Alternative #1: you could change your formula: =Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005)) becomes =Sumproduct(--(A2:A6=1),--(right(Y2:Y6,4)="2005")) ======= Question (that I should have asked first). Did you put that formula in the worksheet that had the data? Alur wrote: I used Range("A7").Formula = "=Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005))" It translated and in the cell A7 the result is 0. Why? "Tom Ogilvy" wrote: in the immediate window of the vbe, put in a command like this Range("AA1").Formula = "=Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005))" then go to the end of that command and hit enter to execute it. Look in cell AA1 and you should see the translated command. -- Regards, Tom Ogilvy "Alur" wrote in message ... Where can i find the same functions (like sumproduct) in other languages ? "Tom Ogilvy" wrote: =Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005)) -- Regards, Tom Ogilvy "Alur" wrote in message ... I have two columns and five rows. A Y 1 11.01.2005 1 12.06.2004 3 10.06.2005 4 15.03.2005 1 10.01.2005 Condition: A=1 and Y=2005 (this year) Requirement: to find the amount of rows with the help of some formula (= and so on) How is it possible to calculate that amount ? -- Dave Peterson |
Calculation of row count
Thank you.
My dates are not text.They have dd.mm.yyyy format. The formula is in the worksheet that had the data. "Dave Peterson" wrote: My guess is that your dates: 1 11.01.2005 1 12.06.2004 3 10.06.2005 4 15.03.2005 1 10.01.2005 Are really text. If you try to format one of those dates (format|Cells|number tab|date category) to a different format, does the cell change? If yes, then I'm wrong. If no, you have text in those cells, not real dates. I'd fix the data. Select that range that contain your Text dates. data|text to columns. fixed width (and remove every line that excel guessed) choose dmy as the format of the date format the cell the way you want (custom as dd.mm.yyyy would give the same look, but the values would be real dates.) ======= Alternative #1: you could change your formula: =Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005)) becomes =Sumproduct(--(A2:A6=1),--(right(Y2:Y6,4)="2005")) ======= Question (that I should have asked first). Did you put that formula in the worksheet that had the data? Alur wrote: I used Range("A7").Formula = "=Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005))" It translated and in the cell A7 the result is 0. Why? "Tom Ogilvy" wrote: in the immediate window of the vbe, put in a command like this Range("AA1").Formula = "=Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005))" then go to the end of that command and hit enter to execute it. Look in cell AA1 and you should see the translated command. -- Regards, Tom Ogilvy "Alur" wrote in message ... Where can i find the same functions (like sumproduct) in other languages ? "Tom Ogilvy" wrote: =Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005)) -- Regards, Tom Ogilvy "Alur" wrote in message ... I have two columns and five rows. A Y 1 11.01.2005 1 12.06.2004 3 10.06.2005 4 15.03.2005 1 10.01.2005 Condition: A=1 and Y=2005 (this year) Requirement: to find the amount of rows with the help of some formula (= and so on) How is it possible to calculate that amount ? -- Dave Peterson |
Calculation of row count
Maybe it's the other side of the formula:
A2:A6=1 Are the values in A2:A6 text or numbers? If you put =count(a2:a6) in one cell and =counta(a2:a6) in another, do you get the same value? If you don't then some of those 5 cells are text and some are numbers. I'd fix my entries to be real numbers (format as general and retype the values). Or (One more guess...) Are the values in A2:A6 really equal to 1. It's not just formatted to show 1. (Kind of 1.0000000000323 formatted to show 1??) And (one more...) Are you sure the columns/ranges are correct? And if you're positive about all this, maybe you don't have 1's in A2:A6 with years of 2005 in Y2:Y6???? Alur wrote: Thank you. My dates are not text.They have dd.mm.yyyy format. The formula is in the worksheet that had the data. "Dave Peterson" wrote: My guess is that your dates: 1 11.01.2005 1 12.06.2004 3 10.06.2005 4 15.03.2005 1 10.01.2005 Are really text. If you try to format one of those dates (format|Cells|number tab|date category) to a different format, does the cell change? If yes, then I'm wrong. If no, you have text in those cells, not real dates. I'd fix the data. Select that range that contain your Text dates. data|text to columns. fixed width (and remove every line that excel guessed) choose dmy as the format of the date format the cell the way you want (custom as dd.mm.yyyy would give the same look, but the values would be real dates.) ======= Alternative #1: you could change your formula: =Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005)) becomes =Sumproduct(--(A2:A6=1),--(right(Y2:Y6,4)="2005")) ======= Question (that I should have asked first). Did you put that formula in the worksheet that had the data? Alur wrote: I used Range("A7").Formula = "=Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005))" It translated and in the cell A7 the result is 0. Why? "Tom Ogilvy" wrote: in the immediate window of the vbe, put in a command like this Range("AA1").Formula = "=Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005))" then go to the end of that command and hit enter to execute it. Look in cell AA1 and you should see the translated command. -- Regards, Tom Ogilvy "Alur" wrote in message ... Where can i find the same functions (like sumproduct) in other languages ? "Tom Ogilvy" wrote: =Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005)) -- Regards, Tom Ogilvy "Alur" wrote in message ... I have two columns and five rows. A Y 1 11.01.2005 1 12.06.2004 3 10.06.2005 4 15.03.2005 1 10.01.2005 Condition: A=1 and Y=2005 (this year) Requirement: to find the amount of rows with the help of some formula (= and so on) How is it possible to calculate that amount ? -- Dave Peterson -- Dave Peterson |
Calculation of row count
Thank you.
"Dave Peterson" wrote: Maybe it's the other side of the formula: A2:A6=1 Are the values in A2:A6 text or numbers? If you put =count(a2:a6) in one cell and =counta(a2:a6) in another, do you get the same value? If you don't then some of those 5 cells are text and some are numbers. I'd fix my entries to be real numbers (format as general and retype the values). Or (One more guess...) Are the values in A2:A6 really equal to 1. It's not just formatted to show 1. (Kind of 1.0000000000323 formatted to show 1??) And (one more...) Are you sure the columns/ranges are correct? And if you're positive about all this, maybe you don't have 1's in A2:A6 with years of 2005 in Y2:Y6???? Alur wrote: Thank you. My dates are not text.They have dd.mm.yyyy format. The formula is in the worksheet that had the data. "Dave Peterson" wrote: My guess is that your dates: 1 11.01.2005 1 12.06.2004 3 10.06.2005 4 15.03.2005 1 10.01.2005 Are really text. If you try to format one of those dates (format|Cells|number tab|date category) to a different format, does the cell change? If yes, then I'm wrong. If no, you have text in those cells, not real dates. I'd fix the data. Select that range that contain your Text dates. data|text to columns. fixed width (and remove every line that excel guessed) choose dmy as the format of the date format the cell the way you want (custom as dd.mm.yyyy would give the same look, but the values would be real dates.) ======= Alternative #1: you could change your formula: =Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005)) becomes =Sumproduct(--(A2:A6=1),--(right(Y2:Y6,4)="2005")) ======= Question (that I should have asked first). Did you put that formula in the worksheet that had the data? Alur wrote: I used Range("A7").Formula = "=Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005))" It translated and in the cell A7 the result is 0. Why? "Tom Ogilvy" wrote: in the immediate window of the vbe, put in a command like this Range("AA1").Formula = "=Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005))" then go to the end of that command and hit enter to execute it. Look in cell AA1 and you should see the translated command. -- Regards, Tom Ogilvy "Alur" wrote in message ... Where can i find the same functions (like sumproduct) in other languages ? "Tom Ogilvy" wrote: =Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005)) -- Regards, Tom Ogilvy "Alur" wrote in message ... I have two columns and five rows. A Y 1 11.01.2005 1 12.06.2004 3 10.06.2005 4 15.03.2005 1 10.01.2005 Condition: A=1 and Y=2005 (this year) Requirement: to find the amount of rows with the help of some formula (= and so on) How is it possible to calculate that amount ? -- Dave Peterson -- Dave Peterson |
Calculation of row count
Thank you.
"Tom Ogilvy" wrote: in the immediate window of the vbe, put in a command like this Range("AA1").Formula = "=Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005))" then go to the end of that command and hit enter to execute it. Look in cell AA1 and you should see the translated command. -- Regards, Tom Ogilvy "Alur" wrote in message ... Where can i find the same functions (like sumproduct) in other languages ? "Tom Ogilvy" wrote: =Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005)) -- Regards, Tom Ogilvy "Alur" wrote in message ... I have two columns and five rows. A Y 1 11.01.2005 1 12.06.2004 3 10.06.2005 4 15.03.2005 1 10.01.2005 Condition: A=1 and Y=2005 (this year) Requirement: to find the amount of rows with the help of some formula (= and so on) How is it possible to calculate that amount ? |
| All times are GMT +1. The time now is 05:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com