+ Reply to Thread
Results 1 to 7 of 7

formula to add $ based upon month

Hybrid View

  1. #1
    Registered User
    Join Date
    09-30-2004
    Posts
    64

    formula to add $ based upon month

    Ok, I've searched for well over an hour and can not quite find what I need.

    I have 2 columns, column A lists dates 1-jan-08 through 12-dec-08 and column B will have $ from individual transactions, other columns have associated info not relavent to my formula needs at the moment.
    example
    A B
    1/2/08 $52.22
    1/5/08 $33.14
    1/7/08 $102.44
    2/3/08 $12.00
    3/31/08 $45.00
    4/7/08 $454.00

    what i want to do is to get a total based upon the month.

    I have seen sumproduct formulas similar to:

    =sumproduct(--(month(a:a)=1),b:b)

    and others that use offsets and just can not seem to get these to work, either I get an error or a zero.

    the output that i am looking for is:
    A B
    Jan $450.00
    Feb $1200.75
    Mar $685.00
    etc

    Ultimately, this formula would appear on a financial summary sheet showing income by month and expense by month

    any help would be appreciated

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Try


    =SUMPRODUCT((TEXT($A$1:$A$6,"MMM")=F1)*($B$1:$B$6))
    Where F1 = Jan

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Registered User
    Join Date
    09-30-2004
    Posts
    64
    Woohoo, it worked!

    Thank you very much.

  4. #4
    Registered User
    Join Date
    09-30-2004
    Posts
    64
    here is another option that someone sent me privately:

    =sumproduct(--(Month(rangewithdates)=1),rangewith$)

  5. #5
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    If you replace 1 with Row(A1) you can drag it down to change months

    =sumproduct(--(Month(rangewithdates)=Row(A1)),rangewith$)
    VBA Noob

  6. #6
    Registered User
    Join Date
    09-30-2004
    Posts
    64
    clever :-)

    thanks again

+ 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