Add addition to a list by year

I'm trying to count the number of new categories that I added to my inventory each year. I'm currenty using countif(Range, ">0") and then subtracting the results from previous years, but I'm having to offset this because sometimes I because I have zero of that item in a subsequent year (e.g., Category 2, 2014). Is there a way I can write a formula to count new categories added by year easily?

For results, I'm expecting 2 for 2013 (since 2 new categories were added), 1 for 2014 (since only Category 3 is a new category), and 0 for 2015 (since no new categories were added).

	2013	2014	2015
Category 1	1	4	0
Category 2	2	0	6
Category 3	0	5	1

There is nothing in the data you provided that shows when a category was added. If you include a date indicating when the category was added, then this task would be possible.

Sorry, I thought it was clear from the description and the chart. Categories 1 and 2 were added in 2013. Category 3 was added in 2014. No categories were added in 2015.

It is not clear. How can I tell by looking at only the data provided when a Category was added? Excel does not read your verbal comments.

I'm aware of that. I have three years in my header so I'm not sure what I'm supposed to add. The first time a Category appears as a non-zero number is the year it was added, and I've also provided the expected results.

Ah, that makes a lot more sense.

The attached makes use of an array formula in a helper column, First Found. Normally you could use MATCH for this, but MATCH looks for specific values. So we have to "translate" >0 to TRUE. =MATCH(TRUE,B2:D2>0,0) Array formulas work on ranges instead of individual cells. So what this formula essentially doing is comparing Cell B2 > 0 and Cell C2 > 0. etc. and converting them inside the array formula to TRUE or FALSE. Then MATCH can look for the true or false.

For more information about array formulas, see this article: http://www.utteraccess.com/wiki/inde...Array_Formulas

Then the number new is a COUNTIF of the results of the array formulas.

Awesome! Thanks. I'm trying to apply this to a much larger workbook I'm working on. This is just a mock-up. For some reason, this isn't translating onto my workbook. MATCH is just appearing as text instead of generating a number. I've created an XLSB file (I was working an XLSX) but can't seem to get the formula to generate a number instead of appearing as text. Do you know why this might be happening? Sorry, I'm a bit of a rookie at this.

Check the format of the cell in which the formula is entered. If it's text, then change it to number. You may have to press F2 as well. Also (donning my Captain Obvious hat) make sure your are starting out with = and that you enter it as an array formula CTRL-ALT-ENTER.

The CTRL-ALT-ENTER made it work. Thank you!