How to convert range of values to 1 and 0 for use in sumproduct

Values      Dates
0       31/12/2020
0       31/12/2021
7       31/12/2022
0       31/12/2023
0       31/12/2024

I have two ranges of data similar to the table above and I would like to use the built-in Sumproduct formula in such a way to return the date of the non-zero value (in this case 31/12/2022). Do anyone know how I can go about this?

I would prefer if this was possible without first creating a third row/column which calculates ones and zeros from the Values range.

>Solution :

Using <> within SUMPRODUCT:


enter image description here

This will return unexpected results if you have more than one nonzero value in column A.

Leave a Reply