Sumproduct Not Recognizing Dates - Help w/ Forumla

[Pistol575]

Hello,

I am trying to write a formula to help with financial disclosures for financing schedules. I need to display the amount of principal owed by year for the next 5 years and thought the best way to do this would be to use a sumproduct formula based up an entry date cell. This way I can automatically adjust the dates using the same workbook.

However, my formulas are not working and I believe it is something to due with the date field. For example, I've written the formula =sumproduct(A1:A5<F2). Cells A1:A5 are months starting on 01/01/23 and cell F2 is the date 06/30/23. I would expect this formula to trigger a result of 5 as it should count 5 values that meet this criteria, but I am getting a result of 0. It seems that this is the starting point of my problem. Can someone help me understand where my thinking is off here?

[AliGW]

Welcome to the forum.

If none of the dates in the range are the same as F2, you won't get a result.

It would be easier if you provided a small, desensitised sample workbook.

[Pistol575]

Hi Ali,

Thank you for the welcoming & response and help - I just uploaded a sample workbook =)

Can you help me explain why the date ranges would need to be the same & wouldn't recognize that they are less than the F2 referenced cell and be counted?

[FlameRetired]

In the meantime you might try this since your formula returns an array of TRUE/FALSE and those need to be coerced into their underlying values of 1/0

Please Login or Register  to view this content.

With dates in A1:A5 I get 5 at my end.

[FlameRetired]

They do return TRUE. This returns 5

Please Login or Register  to view this content.

[Pistol575]

You both are amazing. Thank you so much for the fast responses. I updated the sumproduct formula to include the (--) to coerce the values into 1/0 & they are now being summed and I get a result of 5.

[FlameRetired]

Good deal and you are welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.