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 ?

=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 ?

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 ?

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 ?

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

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

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

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

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 ?