Formula to calculate days in month Item is Held

[Prof Sick]

Hi,

In the attached file, in the area highlighted in yellow, I need help trying to figure out how to populate in each month, how many days a item will be held until sold. There is a purchase date and a sale date referenced. This will calculate how many days the item is held.

I'm trying to figure out how many days it will be held in month 1, month 2, month 3 etc. I've filled in the cells manually for purposes of example.

Thanks in advance.

-Manny

[Phuocam]

Try this in F5:

=SUMPRODUCT(--(TEXT(ROW(INDIRECT($C5&":"&$D5)),"mmm")=LEFT(F$3,3)))

[Prof Sick]

Worked Perfectly. Thanks for the help!

-Manny

[Rick Rothstein]

Try this in F5:

=SUMPRODUCT(--(TEXT(ROW(INDIRECT($C5&":"&$D5)),"mmm")=LEFT(F$3,3)))

The above formula is Volatile... we can rewrite it as below to make it non-Volatile.

=SUMPRODUCT(--(TEXT(ROW(INDEX(A:A,$C5):INDEX(A:A,$D5)),"mmm")=LEFT(F$3,3)))

[ChemistB]

Much simpler than my solution of

=IF(OR($D5<F$4, EOMONTH(F$4,0)<$C5),0, MIN(MAX(EOMONTH(F$4,0),$C5),$D5)-MAX($C5,F$4)+1)

Where row 4 was the date, first of each month.

[Bo_Ry]

Another one
F5
=MAX(0,MIN(EDATE(F$3&2020,1),$D5+1)-MAX(--(F$3&2020),$C5))