Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    New Lounger
    Join Date
    Jan 2014
    Posts
    18
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Excel COUNTIFS help

    Hi all

    Ok before we begin let me make it 100% clear. I am a total novice with excel.

    I have created a spread sheet that I can enter info into regarding Non Conformances. I then created table and graphs with that data. I figured out how to do this by running into a problem and messing around until it works. so I realize that the programming is a mess.

    Sheet 1 is where the info is entered and sheet two is where it is displayed.

    My current problem is that I want only the current years data to be displayed "2014". I made the table and graph add up the cost in the current year by using the below code

    =SUMIFS(Sheet1!G2:G2007,Sheet1!H2:H2007,"=Fab Welding",Sheet1!E2:E2007,">=1/1/2014")

    What I can't figure out is how do I get the "# of NCR's" to only include the current year

    What would I add to =COUNTIF(Sheet1!H2:H1002, "Fab Welding") to make it only include the current year.

    Also instead of using ,">=1/1/2014" and having to change the specific year every year is there a way to just make it the current year.

    Thank you in advance for any assistance you can offer.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    in C2:
    =SUMIFS(Sheet1!$G$2:$G$2007,Sheet1!$H$2:$H$2007,B2 ,Sheet1!$E$2:$E$2007,">=1/1/" & YEAR(TODAY()))

    Copy down the column.

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    timberwolf (2014-01-09)

  4. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You can also use in A2:
    =COUNTIFS(Sheet1!$H$2:$H$1002,B2)

    and copy it down to avoid explicitly typing in the "area" in the formula and just reading the cell.

    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    timberwolf (2014-01-09)

  6. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    T.W.

    Welcome to the Lounge as a new poster.

    Here's another idea. If your sheet has multiple years you can place the year in Sheet2!A11 then this formula in A2 and copy down.
    =COUNTIFS(Sheet1!$H$2:$H$2001,$B2,Sheet1!$E$2:$E$2 001,">=1/1/"&$A$11,Sheet1!$E$2:$E$2001,"<=12/31/"&$A$11)
    Now just changing the year in A11 will change all the calcs & graph.
    countif.JPG
    Of course you can change your sumifs to reference A11 the same way.
    HTH
    Last edited by RetiredGeek; 2014-01-09 at 15:44.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. The Following User Says Thank You to RetiredGeek For This Useful Post:

    timberwolf (2014-01-09)

  8. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    TW,

    Here's the formula for the Totals column.
    =SUMIFS(Sheet1!$G$2:$G$2001,Sheet1!$H$2:$H$2001,$B 2,Sheet1!$E$2:$E$2001,">=1/1/"&$A$11,Sheet1!$E$2:$E$2001,"<=12/31/"&$A$11)
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. The Following User Says Thank You to RetiredGeek For This Useful Post:

    timberwolf (2014-01-10)

  10. #6
    New Lounger
    Join Date
    Jan 2014
    Posts
    18
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Thank you both for your speedy replies

    I tried using the below code because I would like to be able to change the viewed year.

    =COUNTIFS(Sheet1!$H$2:$H$2001,$B2,Sheet1!$E$2:$E$2 001,">=1/1/"&$A$11,Sheet1!$E$2:$E$2001,"<=12/31/"&$A$11)

    When I entered the formula I got this error

    Capture.JPG

    I have attached my whole table in the hopes this would help
    NCR LIST IN-House & Fab TEST.xlsx

    Thank you so much for all your assistance I've been going nuts trying to get this to work lol

  11. #7
    New Lounger
    Join Date
    Jan 2014
    Posts
    18
    Thanks
    13
    Thanked 0 Times in 0 Posts
    I fixed the space in the formula but now it Just shows a 0 value for A2

  12. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The file you attached has no 2014 data, only 1 piece of data from 2012, so 0 should be the result.

    Steve

  13. The Following User Says Thank You to sdckapr For This Useful Post:

    timberwolf (2014-01-10)

  14. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hey Guys,

    My bad I forgot to mention that I put in some test data when I took the screen shot.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. The Following User Says Thank You to RetiredGeek For This Useful Post:

    timberwolf (2014-01-10)

  16. #10
    New Lounger
    Join Date
    Jan 2014
    Posts
    18
    Thanks
    13
    Thanked 0 Times in 0 Posts

    I'm still doing something wrong...

    Good morning Gentlemen

    As you can see from the attached file I copied in the formulas and added in some test information but for some reason I am making a mistake still as it is not working.

    Could you look at the attached file and see where my mistake(s) lie.

    Again Gentlemen thank you for all your help.




    NCR LIST IN-House & Fab TEST.xlsx

  17. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    T.W.,

    I just downloaded your file. I changed the date in Sheet2!A11 to 2013 and it worked fine. I changed it back to 2014 and it also worked. Don't know why it isn't working for you but you might want to check if your recalculation is set to Manual. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  18. The Following User Says Thank You to RetiredGeek For This Useful Post:

    timberwolf (2014-01-10)

  19. #12
    New Lounger
    Join Date
    Jan 2014
    Posts
    18
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Here are the formula settings.settings.JPG

    I'm sorry to keep pestering you.

  20. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    T.W.,

    I don't know why it won't work for you. Have you tried a manual recalc or changing the year in Sheet2!A11?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  21. The Following User Says Thank You to RetiredGeek For This Useful Post:

    timberwolf (2014-01-10)

  22. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The file works for me as well. As RG indicated, I can change A11 do different years and get different results.

    Steve

  23. The Following User Says Thank You to sdckapr For This Useful Post:

    timberwolf (2014-01-10)

  24. #15
    New Lounger
    Join Date
    Jan 2014
    Posts
    18
    Thanks
    13
    Thanked 0 Times in 0 Posts
    I'm really at my wits end with this problem.

    As you can see the formula is looking in the right spot but is still returning a 0 value.

    There must be a option somewhere in my excel program that is set wrong.

    Capture.JPGCapture2.JPG

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

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