+ Reply to Thread
Results 1 to 11 of 11

Calculation of row count

  1. #1
    Alur
    Guest

    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 ?


  2. #2
    Tom Ogilvy
    Guest

    Re: Calculation of row count

    =Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005))

    --
    Regards,
    Tom Ogilvy


    "Alur" <[email protected]> wrote in message
    news:[email protected]...
    > 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 ?
    >




  3. #3
    Alur
    Guest

    Re: Calculation of row count

    Thank you.

    "Tom Ogilvy" wrote:

    > =Sumproduct(--(A2:A6=1),--(Year(Y2:Y6)=2005))
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "Alur" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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 ?
    > >

    >
    >
    >


  4. #4
    Alur
    Guest

    Re: Calculation of row count

    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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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 ?
    > >

    >
    >
    >


  5. #5
    Tom Ogilvy
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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 ?
    > > >

    > >
    > >
    > >




  6. #6
    Alur
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > 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 ?
    > > > >
    > > >
    > > >
    > > >

    >
    >
    >


  7. #7
    Dave Peterson
    Guest

    Re: 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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" <[email protected]> wrote in message
    > > > > news:[email protected]...
    > > > > > 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

  8. #8
    Alur
    Guest

    Re: 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" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > 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" <[email protected]> wrote in message
    > > > > > news:[email protected]...
    > > > > > > 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
    >


  9. #9
    Dave Peterson
    Guest

    Re: 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" <[email protected]> wrote in message
    > > > > news:[email protected]...
    > > > > > 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" <[email protected]> wrote in message
    > > > > > > news:[email protected]...
    > > > > > > > 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

  10. #10
    Alur
    Guest

    Re: 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" <[email protected]> wrote in message
    > > > > > news:[email protected]...
    > > > > > > 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" <[email protected]> wrote in message
    > > > > > > > news:[email protected]...
    > > > > > > > > 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
    >


  11. #11
    Alur
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > 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 ?
    > > > >
    > > >
    > > >
    > > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1