Product and IF multiplication of percentage on range in different tab

I tried this formula, but am getting 0% with error, the formula in the cell refers to a range that has additional numbers adjacent to it. I tried to select omit adjacent but still 0%

Tab Summary set as percentage
=PRODUCT(IF('Current State'!F2:F50<>0|'Current State'!F2:F50))

Tab Current State
values F2:F21 (has value > 0% 1-100%) and F22:F50 are 0%
100%
75%
50%
25%
75%
50%
25%
100%
75%
50%
100%
75%
50%
25%
75%
50%
25%
100%
75%
50%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%
0%

Hi, welcome to the forum.

What is it that you are trying to do? Multiply all of the percentages that are not 0?

correct thanks

Then your formula should work, assuming that you are using the pipe symbol "|" instead of commas.

Just make sure that you confirm the formula using Ctrl Shift Enter instead of just Enter.

You will get a very small number (0.00001086 from your example).

Thanks, yes I'm using pipes. must be an excel issue. It works for =PRODUCT(IF('Current State'!F2:F8<>0|'Current State'!F2:F8)) and I get .88% but if I do F2:F50 and expands the decimal out 10 places I still get 0.

I'm not sure why the formula is not working for you. It is working on my end.

See attached.

tried using your excel and copied formula to sheet3 and referenced sheet1 and got 0

=PRODUCT(IF(Sheet1!F2:F50<>0|Sheet1!F2:F50))

Are you confirming the formula with Ctrl+Shift+Enter instead of just Enter?

This is an array formula and must be entered in this manner.

I get 0 if I simply hit Enter after typing the formula in.

Thank Yup worked on your worksheet. It wasn't working on mine but will try again. thanks!

You're welcome, happy to help.

If that solved your question, please mark this thread as SOLVED.

If you are still unable to get it to work on your end, consider uploading the workbook here so that we can take a closer look at it.

quick response and very helpful

Happy to help. Thanks for coming back to let us know that it worked for you.