Help with Excel formulas

Hi Edward R,

The Array Formula that I have written works for multiple years.

=SUM(SUM(IF($B$1:$U$1=$W$2,$B$2:$U$367,0)*IF($A$2:$A$367+DATE($W$2,1,0)>=DATE($W$2,$X$2,$Y$2),1,0)),SUM(IF($B$1:$U$1>$W$2,$B$2:$U$367,0)*IF($B$1:$U$1<$W$3,1,0)),SUM(IF($B$1:$U$1=$W$3,$B$2:$U$367,0)*IF($A$2:$A$367+DATE($W$3,1,0)<=DATE($W$3,$X$3,$Y$3),1,0)))/SUM(SUM(IF($B$1:$U$1=$W$2,IF(ISNUMBER($B$2:$U$367),1,0),0)*IF($A$2:$A$367+DATE($W$2,1,0)>=DATE($W$2,$X$2,$Y$2),1,0)),SUM(IF($B$1:$U$1>$W$2,IF(ISNUMBER($B$2:$U$367),1,0),0)*IF($B$1:$U$1<$W$3,1,0)),SUM(IF($B$1:$U$1=$W$3,IF(ISNUMBER($B$2:$U$367),1,0),0)*IF($A$2:$A$367+DATE($W$3,1,0)<=DATE($W$3,$X$3,$Y$3),1,0)))

I have designed it for Numerical data, not for non-numerical data. Cells with non-numerical data should be kept blank.

The Start Year, Month and Day as well as the End Year, Month and day can be entered independently in cells W2 to Y3.

I have also changed the year (column A) to 1900, instead of 2018.

The formula is written for 20 columns (20 years), but can be extended.

Array Formulas are written with Control + Shift + Enter, instead of Enter. The Formula is in cell AA2 in the attached Excel File.

Refer to the attached Excel file for the formula.

Since you have only 2 years of data (2 columns), probably the formula may not be required for you for the current data. If the number of years increase, it will help you.

Vijaykumar Shetye,

Panaji, Goa, India