Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

Using <> within SUMPRODUCT:

=SUMPRODUCT(B2:B6*(A2:A6<>0))

enter image description here

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

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading