+ Reply to Thread
Results 1 to 3 of 3

Calculating montlhy totals using sumproduct (or other)

  1. #1

    Calculating montlhy totals using sumproduct (or other)

    Hi there!

    I have been looking in past post, and not finding the solution to what
    I am trying to do.

    Here is an example of the entry sheet I am trying to look into;
    I have remove some extra colomns
    Colom A formatted as a date format, entering data as dd-mm-year and
    shown as in example

    A H
    1 DATE
    2 may 2, 2006
    3 =SUM(F2:F4)
    4
    5 may 3, 2006 =F5
    6 May 4, 2006
    7 =sum(F6:F7)
    8 May 8, 2006 =F8
    ..
    ..
    20 June 1, 2006
    21 =sum(F20:F21)
    22 june 2, 2006 =F22

    So basically I enter some amount each day, some time more then once and
    make a summary of it in the H colomn.

    Now in another cell, I want to calculate the total for the individual
    months.
    I have been trying to use the formula
    =SUMPRODUCT((MONTH(A2:A22)=6),H2:H22)
    for the total of June, but keep getting a #VALUE error.

    What am I doing wrong, or is there another for me to accomplish what I
    am trying to do?
    Should I be adding on each row the date instead of leaving blanks?
    (tried but same result)

    Thanks in advance for any help, tip and pointers to help me solve this.

    Serge


  2. #2
    Marcelo
    Guest

    RE: Calculating montlhy totals using sumproduct (or other)

    Hi,

    try it

    =SUMPRODUCT(--(MONTH(A2:A22)=6)*(H2:H22))

    hope this helps
    Regards from Brazil
    Marcelo

    "[email protected]" escreveu:

    > Hi there!
    >
    > I have been looking in past post, and not finding the solution to what
    > I am trying to do.
    >
    > Here is an example of the entry sheet I am trying to look into;
    > I have remove some extra colomns
    > Colom A formatted as a date format, entering data as dd-mm-year and
    > shown as in example
    >
    > A H
    > 1 DATE
    > 2 may 2, 2006
    > 3 =SUM(F2:F4)
    > 4
    > 5 may 3, 2006 =F5
    > 6 May 4, 2006
    > 7 =sum(F6:F7)
    > 8 May 8, 2006 =F8
    > ..
    > ..
    > 20 June 1, 2006
    > 21 =sum(F20:F21)
    > 22 june 2, 2006 =F22
    >
    > So basically I enter some amount each day, some time more then once and
    > make a summary of it in the H colomn.
    >
    > Now in another cell, I want to calculate the total for the individual
    > months.
    > I have been trying to use the formula
    > =SUMPRODUCT((MONTH(A2:A22)=6),H2:H22)
    > for the total of June, but keep getting a #VALUE error.
    >
    > What am I doing wrong, or is there another for me to accomplish what I
    > am trying to do?
    > Should I be adding on each row the date instead of leaving blanks?
    > (tried but same result)
    >
    > Thanks in advance for any help, tip and pointers to help me solve this.
    >
    > Serge
    >
    >


  3. #3
    Serge Ayotte
    Guest

    Re: Calculating montlhy totals using sumproduct (or other)

    Thank you Marcelo!

    After playing around another hour or so, I got it working, but I had to
    add the date of every line which I skipped when having multiple entry
    the same date!

    Again a BIG thank you.

    My regard from qauebec, Canada!
    Serge

    Marcelo wrote:
    > Hi,
    >
    > try it
    >
    > =SUMPRODUCT(--(MONTH(A2:A22)=6)*(H2:H22))
    >
    > hope this helps
    > Regards from Brazil
    > Marcelo
    >
    > "[email protected]" escreveu:
    >
    > > Hi there!
    > >
    > > I have been looking in past post, and not finding the solution to what
    > > I am trying to do.
    > >
    > > Here is an example of the entry sheet I am trying to look into;
    > > I have remove some extra colomns
    > > Colom A formatted as a date format, entering data as dd-mm-year and
    > > shown as in example
    > >
    > > A H
    > > 1 DATE
    > > 2 may 2, 2006
    > > 3 =SUM(F2:F4)
    > > 4
    > > 5 may 3, 2006 =F5
    > > 6 May 4, 2006
    > > 7 =sum(F6:F7)
    > > 8 May 8, 2006 =F8
    > > ..
    > > ..
    > > 20 June 1, 2006
    > > 21 =sum(F20:F21)
    > > 22 june 2, 2006 =F22
    > >
    > > So basically I enter some amount each day, some time more then once and
    > > make a summary of it in the H colomn.
    > >
    > > Now in another cell, I want to calculate the total for the individual
    > > months.
    > > I have been trying to use the formula
    > > =SUMPRODUCT((MONTH(A2:A22)=6),H2:H22)
    > > for the total of June, but keep getting a #VALUE error.
    > >
    > > What am I doing wrong, or is there another for me to accomplish what I
    > > am trying to do?
    > > Should I be adding on each row the date instead of leaving blanks?
    > > (tried but same result)
    > >
    > > Thanks in advance for any help, tip and pointers to help me solve this.
    > >
    > > Serge
    > >
    > >



+ 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