1. 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.

2. 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. 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. 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

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

timberwolf (2014-01-09)

8. 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

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

timberwolf (2014-01-10)

10. 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. I fixed the space in the formula but now it Just shows a 0 value for A2

12. 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. Hey Guys,

My bad I forgot to mention that I put in some test data when I took the screen shot.

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

timberwolf (2014-01-10)

16. 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. 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

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

timberwolf (2014-01-10)

19. Here are the formula settings.settings.JPG

I'm sorry to keep pestering you.

20. 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?

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

timberwolf (2014-01-10)

22. 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. 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 Last