this worksheet checks that values on the range from A:E all the way down to match the one from G:K on the first row
I tried using COUNTIFS, MATCH with SUM, SUMPRODUCT and the best I could do was hardcoding the array. It will be productive to change this to check the values from G to K dinamically and not hardcode as it is
Any ideas to improve this formula?
=IF(SUM(COUNTIF(A1:E1,{"8","19","26","27","34"}))=5,$L$1,
IF(SUM(COUNTIF(A1:E1,{"8","19","26","27","34"}))=4,$M$1,
IF(SUM(COUNTIF(A1:E1,{"8","19","26","27","34"}))=3,$N$1,
IF(SUM(COUNTIF(A1:E1,{"8","19","26","27","34"}))=2,$O$1,
IF(SUM(COUNTIF(A1:E1,{"8","19","26","27","34"}))<2,"L","F")))))
I tried using COUNTIFS, MATCH with SUM, SUMPRODUCT and the best I could do was hardcoding the array. It will be productive to change this to check the values from G to K dinamically and not hardcode as it is
Any ideas to improve this formula?
=IF(SUM(COUNTIF(A1:E1,{"8","19","26","27","34"}))=5,$L$1,
IF(SUM(COUNTIF(A1:E1,{"8","19","26","27","34"}))=4,$M$1,
IF(SUM(COUNTIF(A1:E1,{"8","19","26","27","34"}))=3,$N$1,
IF(SUM(COUNTIF(A1:E1,{"8","19","26","27","34"}))=2,$O$1,
IF(SUM(COUNTIF(A1:E1,{"8","19","26","27","34"}))<2,"L","F")))))