Forum Discussion
Need help with excel
Assuming you always want a count of 1 when the month column is encompassed by the start & end dates of the row (I mention this as in the image example some fields have 2 or 3 and I don’t understand why)…
The fields for start date, end date and the headers for the month columns will all need to be formatted as date cells.
The rest of the table will need to be formatted as number cells.
This approach takes the header month date and asks if the ‘period begin start of month’ is before the header month ended AND if the ‘period end end of month’ in is after the header month began.
The formula:
=if(AND(EOMONTH($A2,-1)+1<=EOMONTH(C$2,0),EOMONTH($B2,0)>=EOMONTH(C$2,-1)+1),1,0)
I now realise what I failed to comprehend in the original post, in part thanks to the solutions by others, so see now the significance of the first table and why some months have a count other than 1.
I’ll leave this reply though rather than delete it in case it does offer some value, perhaps to someone else with a similar but different issue that consults this thread.