Count if with conditions...

Hi People,

I am looking for a way to resolve this issue. But i would have to explain a little...

Every equipment should be tested one year after set up. Then this test should be repeated every 2 years after the first test for the life of the equipment. The equipment life is 15 years. lets call this the certification test
However, in addition, there is an overhaul done every 5 years. this overhaul test can also coincide with the certification test. These are separate tests.

The test starts on the date of the title transfer. so all equipment within a given year, should be ready for the first test by the end of the first year.

For example, lets assume title transfer occurred Jan 2007. by Jan 2008 the first certification test check is due. By Jan 2010, the next certification test is due. By Jan 2012, the overhaul and certification test are due.

However, for equipment with title transfer in 2009, these set will also be included with the original Jan 2007 set in jan 2010. etc

The same rule applies to all equipment with title transfer in 2008, 2010, etc.

The question now is : Is there a way to capture these tests per year on a column starting from AS in my attachment?

I appreciate your help!

Almost certainly there will be a way and I was with you until you mentioned what seems to be special rules for transfers in 2003 and mention of 2000 & 2005. It doesn't help that the earliest date is in any case 2007.

As always with these things it's much better if you manually add the results you expect so that we can understand the end goal. If you add these results and then upload the workbook again, explaining this '2003' situation a little more then no doubt we can help further. Be sure to include all year that are relevant - which implies 2003 and later.

Hi,

Would you also explain how you have calculated the numbers in the table with some narrative that refers to the original data. e.g. Certification total 654 for 2010 counts Cells En:En, En:En..etc where E cell is......whatever the rule may be

Hi,

When I filter column E and count the filtered rows :-

For AX8 - 2007 & 2009 I count 652 not 654
For AX9 - 2008 & 2010 I count 741 not 743
For AX10 - 2007 & 2009 & 2011 I count 1082 not 1332
For AX11 - 2008 & 2010 & 2012 I count 1145 not 1148

Can you explain these discrepancies since clearly I'm not understanding your rules.

...But what about the much bigger difference with AX10?

Hi,

Does the attached offer you a way forward.

There may be a more complex array formula somewhere hereabouts but perhaps this pragmatic approach which uses helper columns is suitable.

Hi,

That's a totally different requirement to the earlier Q. There you were counting things. i.e. you were getting a single number for the year in question. What you're now asking for is to list many 'things' for the year in question. Which doesn't involve a formula and of course is arguably much easier if you use a Pivot Table. I have assumed you want to see a list of unique serial numbers in each year rather than a list of unique numbers across all years.

The Pivot Table uses columns A,B&D from the A:E range on Sheet1

Hi,

Been struggling a bit with this, not least because during the development & checking the array formulas take 30 seconds or so each time the filter is changed.

Anyway here's the latest. I've added two Pivot Tables each selecting a different alternate series of years. You could of course just use one and change the years each time.