[Solved] How do I SUM with this condition?

[Ziggletooth]

Hello.

I have a list of values of which I want to find the average.
I am using this formula

=SUM(C$302:C313)/(A313-300)

The C column contains 1 or 0. However I want to update my spreadsheet so that it is possible for the C column to contain 2. I have modified my other columns to reflect this but not sure how to modify this formula that deals with a range.

Essentially I want the same formula except if there's a 2 I want it to be read as a 1, so basically summing the existence of a value that isn't 0 rather than the values themselves as it were.

Further clarity, right now I have 60% for values in the C column 1, 0, 1, 0, 1, but I want that to be true for the values 2, 0, 1, 0, 1 (still 60%) etc

Thank you.

[FJCC]

Try

Code: Select all

SUMPRODUCT(C$302:C313 > 0)/(A313-300)

[Ziggletooth]

Thank you very much