Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    SUMIF Function (Excel XP)

    I have a spreadsheet that I use to track investments - profits, losses etc. - so that I can determine any capital gains in a given year. Because investments may be bought in one year and sold in another year, it is the year they are sold in that you have to pay capital gains tax. I want to be able to calculate the capital gains using the SUMIF function. I have created a variable in a cell and called it "Year". I want to be able to write a SUMIF function as follows

    =SUMIF(A1:A50,=Year,B1:B50)

    I have tried a number of variations on the criteria part of this, but cannot get it to work properly. It either gives me error messages or gives me a 0.00 result, when I know there are gains to be found. Any insight or help that anyone can provide to help me get this working, will be greatly appreciated. <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

    Thanks.

    Ron M <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: SUMIF Function (Excel XP)

    Year is the name of an Excel function which may be part of the problem. Try renaming the cell tax_year then use the formula:
    =sumif(A1:A50,tax_year,B1:B50)
    If that doesn't work, can you post a sample of your data for testing?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SUMIF Function (Excel XP)

    Assuming the Year is a named range, you just need to drop the equal sign. See attached sample. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: SUMIF Function (Excel XP)

    Rory, that did it. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Thanks for the insight. I had forgotten that "Year" is a reserved word in Excel.

    Ron M

Posting Permissions

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