How to limit the user to enter no more than 2 values.

[scout51]

I have 4 cells choosen, (A22, B22, C22, D22) but only one or maximum two cells will be fill with a value. (could be any 1 or any 2 of the four cells)
Now, I have 2 problems...
1.- how can I make a formula displaying the average of the value or 2 values entered disregarding the other 2 cells (empty or with a pre determined value of "zero") ?? ======== UPDATE: I received this answer: =AVERAGEIF(A22:D22,"> 0") and it works perfectly so now I now only have one issue left... What can I do to limit the user to only enter 1 or 2 values and somehow block the possibility to enter more than 2 in this 4 cells ??
Hope you can help me.
Thanks

[dilipandey]

HI Scout51,

Use below formula in data validation:-

=COUNTA($A22:$D22)<=2

See attached:less than or equal to 2 values validation.xlsx


Regards,
DILIPandey

<click on below 'star' if this helps>

[scout51]

Dear Sir, the problem is that the 2 fields allowed are not necesarily the first 2 cells... it can be the 1st and the 4th or the 2nd and the 3rd.. etc etc On the worlksheet that you sent me allow the first two cells (A22, B22) but for example if i choose to fill my 2 values in A22 and C22, it wont allow me to do so...

[timtim89]

Try this one in your datavalidation for the cells. Please see attachment.

Please Login or Register  to view this content.

[vlady]

try

=COUNT($A22:$D22)<2 - untick the "ignore blank"

[vlady]

thanks for the rep.
kindly mark this thread solved.
thanks.

[dilipandey]

if i choose to fill my 2 values in A22 and C22, it wont allow me to do so...

Hi Scout.. it is allowing, see attached :-

less than or equal to 2 values validation.xlsx
Regards,
DILIPandey

<click on below 'star' if this helps>