+ Reply to Thread
Results 1 to 5 of 5

Help with SumIf with two conditions

Hybrid View

  1. #1
    Registered User
    Join Date
    11-01-2006
    Posts
    18

    Help with SumIf with two conditions

    I searched the forum and found some help with a personal expense tracking spreadsheet I'm creating for myself, but I need to add a second condition to the SumIf command. Is this possible or is there another way to accomplish this?

    My spreadsheet is a very basic expense log with 4 columns: A=Date, B=Amount, C=Category (Gas, Food, Tuition, etc.) & D=Type (Cash, Check, VISA, etc.). To summarize the expenses, I am trying to create a table with the categories (going down) and the months (going across). To populate the first cell in the table, I need to sum all amounts spent on Gas in January. The formula =SUMIF(C:C,"Gas",B:B) allows me to sum all amounts spent on gas, but how can I add the second condition (spent in January) in the same cell formula? Once I figure this out, I can place similar formulas in the remaining table cells.

    Thanks for your time!

  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
    Hi,

    Try Sumproduct.

    Note you can't use the whole column like sumif.

    Also a warning. It will sum any year with month of Jan

    =SUMPRODUCT(--(TEXT($A$2:$A$1000,"mmm")="Jan")*($C$2:$C$1000="Gas")*($B$2:$B$1000))

    or

    =SUMPRODUCT(--(MONTH($A$2:$A$1000)=1)*($C$2:$C$1000="Gas")*($B$2:$B$1000)))

    This link explains more about sumproduct

    http://www.xldynamic.com/source/xld.SUMPRODUCT.html


    VBA Noob
    Last edited by VBA Noob; 01-07-2007 at 12:08 PM.
    _________________________________________


    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
    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
    Hi again.

    Here's another way

    =SUMPRODUCT(--($A$2:$A$1000>=DATEVALUE("01/01/2007"))*($A$2:$A$1000<=DATEVALUE("31/01/2007"))*($C$2:$C$1000="Gas")*($B$2:$B$1000))

    VBA Noob

  4. #4
    Registered User
    Join Date
    11-01-2006
    Posts
    18
    I used the date range example and it works great! For some reason, the MONTH($A$2:$A$1000)=1 method didn't work when I changed the values for subsequent months (2, 3, 4) even when I changed date values in the log to be dates in Feb, March, etc. with matching categories. Strange.

    Thanks for your suggestions...I appreciate it!

  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
    No problem

    Glad you got a solution

    VBA Noob

+ 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