Forum Discussion

Gaboriar's avatar
Gaboriar
Copper Contributor
Dec 30, 2020

advanced If / And formula =AND(EXACT

Hi

I have data in a column. Some cells have blanks. I wont a true or false checking if all data in the column is the same BUT ignoring blank cells. I'm using below which works but blanks give back a FALSE. I want it to ignore blanks and only compare populated data. 

 

=AND(EXACT

Formulas and Functions

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Dec 30, 2020

    Gaboriar 

    Let's say the range is H1:H7.

    Use the following formula and confirm it with Ctrl+Shift+Enter to turn it into an array formula:

     

    =SUM(IF(H1:H7<>"",1/COUNTIFS(H1:H7,"<>",H1:H7,H1:H7)))=1

Resources