Excel Formula: Excluding some columns from formula based on header

0
votes

I have a COUNTIF formula that counts a particular value across a range of columns in each row. However, I would like to exclude the cell values in a particular column based on the column header.

For example, I need to count the number of cells that contain the value 'NS' across a range 'C:Z' for each row in the worksheet. However, I need to exclude the values in cells that have a column header 'RC'. The challenge is, I cannot control where these 'RC' columns appear in the range. Therefore, I am looking for some mechanism through which I can check whether the column header is 'RC' or not, and if it is, then exclude the cell values from the COUNTIF formula.

Is there anyway to do this?

1
excelmacosexcel-formula
What about COUNTIFS? - BigBen
I am using a COUNTIF to count the number of 'NS' values across the range 'C:Z' in each row. However, like I said, I am trying to exclude all the cells which belong to a column that has an 'RC' header. Is there anyway to reflect this condition within the COUNTIF formula as well? - qr123
What is your exact COUNTIF formula? Are you looking for something like =COUNTIFS(C1:Z1,"<>RC",C2:Z2,"NS")? - BigBen
=COUNTIF(SheetName!C3:Z3, "NS") and this formula is copied down to all the filled rows in the worksheet. - qr123
@BigBen - what you suggested doesn't appear to be working. It still includes all the cells that are in columns with an 'RC' header. - qr123

1 Answers

1
votes

Use COUNTIFS:

=COUNTIFS(C$1:Z$1,"<>RC",C3:Z3,"NS")

enter image description here

EDIT:

As noted by @JvdV, if you are just looking for a single formula to count all instances (i.e. not row-by-row), you can use =SUMPRODUCT((C1:Z1<>"RC")*(C2:Z100="NS")), changing the 100 to your last row as necessary.