If statement using Column Headers rather than cell references.

Question

I have the usual if statement (=IF(AND(K4="Yes",O4=100%),1,0)) however rather than reference the cell as K4 I want to reference by the column header.

Is this possible ?

Answer 1

The @ means "For this row within the table"   So you want the entire column within the table:

=COUNTIFS(Table1[Contract Deliverable],"Yes")

And for the It is, if that is the entirety of the cell entry:

=COUNTIFS(Table1[Contract Deliverable],"It is")

Note that you don't need the = sign ever for the criteria argument. (You also don't need COUNTIFS if you have only one criteria, but it is OK to use it instead of COUNTIF.)

Answer 2

Select your table, with the headers, and choose  Insert / Table

Make sure you have "My table has headers" checked, then press OK

Your range will be converted to a table:

And when you reference cell K4, you will get the table-style reference:

Answer 3

sounds good but not sure how

Answer 4

BTW you do not need an IF for this.

Try --AND(K4="Yes",O4=100%)  note the double negation before AND

OR use (K4="Yes")*(O4=100%)

best wishes

Answer 5

If you convert your range to a table, then the formula will show the correct header reference when you re-enter the formula.  If you want to edit the formula, press F2, select the K4 in the formula and re-select K4 in the newly-defined table, and that will work too.