I am attempting to calculate a percentage of values that are within a year and counting blank values as zero all while ensuring if new rows are created, that cell is then included in the percentage.
=SUMPRODUCT(--($E$5:$E$200>=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))*(($E$5:E$200)<=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))))/COUNTA($E$5:$E$200)
but when I use that comes back as 100%. The only way it works is if I input a 0 in the cell in order to change it to 0-Jan-00.
Bookmarks