Count yes/no in years with dates given

[tonilony]

Hello Forum-Members,

I was wondering if you could help me out with a formular. I would like to count amount of yes/no at specific dates but for years 2015,2014,2013...

Unfortunately I have no idea how to solve this problem - attached excel file is an example of my list.

Thanks for your help - highly appreciated.

Best, Anton

[Pete_UK]

Put this formula in cell F2:

=SUMPRODUCT((YEAR($A$2:$A$8)=$E2)*($B$2:$B$8=F$1))

Copy into G2, then copy those two formulae down.

Hope this helps.

Pete

[FDibbins]

Try this, copied down and across...
=COUNTIFS($A:$A,">="&DATE($E2,1,1),$A:$A,"<="&DATE($E2,12,31),$B:$B,F$1)

[popipipo]

Or a pivot table will do
All three in this file

[FDibbins]

Pivot Table and SUMPRODUCT are both very good options, but both would potentially need the ranges to be adjusted as more data is added...countifS() using column ranges does not need adjusting

[dflak]

Pivot Table and SUMPRODUCT are both very good options, but both would potentially need the ranges to be adjusted as more data is added...countifS() using column ranges does not need adjusting

If the source data is converted into an Excel Table, then this will not be an issue.

[FDibbins]

If the source data is converted into an Excel Table, then this will not be an issue.

True, but that was not a suggestion - so far