Excel 2010 Specific cells for formula input cell arguements

Question

     Col A  Col B  Col C
Row 1  a      a      a  
Row 2  a      a      a  
Row 3  a      a      a  

I am trying to set a specific range in the COUNTIF function, =COUNTIF(range,criteria).

Example 1: Using the sample data above, I would like to see how many occurrences of the letter "a" in Row 1, Column A. This can be done by simply looking at the single cell, A2.

=COUNTIF(A1,"a")

Example 2: I would like to see how many "a" occur in all of Row 2. This can be done using the range from A2:C2.

=COUNTIF(A2:C2,"a")

Example 3: This is where I am stuck. I would like to see how many "a" occur in Row 3, Column A & C only. I can't figure out how to input this into the formula.

=COUNTIF(????????,"a")

EDIT: Thank you all for the quick responses. The reason I asked this question is because I am really working on the following problem:

I have manually inputted data in columns A - H. Column A & B combined create one of four ratings. C & D create the second rating. E & F create the third. G & H create the forth rating. I have combined these four ratings and are seen in columns I - L. Of these four ratings (out of 25 combinations, can be repeated) which appear in different order, I need to assign the highest value (1=high, 4=low) into column N. Column M is an extra column I needed to complete the functions required. It outputs the highest rating of the four ratings as a number from 1-25 (the total possible options). From this number, I then index the corresponding rating from the Rating sheet.

The formulas below work just fine. The reason I am asking for help is for two reasons. I want to combine all the work into a single cell (if possible) and remove the added columns I - L where I can easily CONCATENATE() the two manual inputs per rating within a function. I thought it could be done, but what was holding me back primarily is the section of code below:

COUNTIF(I1:L1,Ratings!$A$1:$A$25)

It is holding me back because COUNTIF() can take a range as an input argument, but not separate cells. I want to have (A1&B1),(C1&D1),(E1&F1),(G1&H1) as the "range" part of the argument for the COUNTIF() function. The ratings for each combination available is on the second sheet labeled "Ratings".

Sheet 1: Sheet1

Columns: A  B  C  D  E  F  G  H  I  J  K  L  M  N
  Row 1: D  2  A  5  E  3  E  3  D2 A5 E3 E3   

-Columns A-H are manually inputted.

-Column I is (A1&B1)

-Column J is (C1&D1)

-Column K is (E1&F1)

-Column L is (G1&H1)

-Column M is

  {=IFERROR(MATCH(1,IF(COUNTIF(I1:L1,Ratings!$A$1:$A$25)>0,1,0),0),"")}

Column N is

   =IFERROR(INDEX(Ratings!$B$1:$B$25,M1),"")

The second sheet labeled Ratings is below:

Sheet 2: Ratings
Columns: A   B
Row 1:   A5  1
Row 2:   A4  1
Row 3:   B5  1
Row 4:   A3  1
Row 5:   A2  1
Row 6:   B4  2
Row 7:   B3  2
Row 8:   C5  2
Row 9:   C4  2
Row 10:  D5  2
Row 11:  A1  3
Row 12:  B2  3
Row 13:  B1  3
Row 14:  C3  3
Row 15:  C2  3
Row 16:  D4  3
Row 17:  E5  3
Row 18:  C1  4
Row 19:  D3  4
Row 20:  D2  4
Row 21:  D1  4
Row 22:  E4  4
Row 23:  E3  4
Row 24:  E2  4
Row 25:  E1  4

Hopefully with the full problem at hand, you all can have a better understanding as to what I am trying to accomplish.

Answer 1

=countif(A3,"a")+countif(C3,"a")

or more simply:

=(A3="a")+(C3="a")

Even if you define a Named Range that is disjoint, COUNTIF() will not accept it.

However something like:

=SUMPRODUCT(COUNTIF(INDIRECT({"A3","C3"}),"a"))

will work, but it is no better than the sum of COUNTIF()s.

Answer 2

Here are a few other options:

This is based on the index number:

=SUM(INDEX(((COLUMN(A3:C3) = 1)+(COLUMN(A3:C3) =3))*(A3:C3="a"),))

This is based on a pattern of every other one:

=SUM(INDEX((MOD(COLUMN($A$3:$C$3),2)=1)*($A$3:$C$3="a"),))

This is based on the values in Row 1:

=SUM(INDEX(($A$1:$C$1="Yes")*($A$3:$C$3="a"),))

Answer 3

I found a better solution with the help of a friend. It consists of replacing the COUNTIF() method with MIN(VLOOKUP(),VLOOKUP(),VLOOKUP(),VLOOKUP()) to find the highest rating of the 4 given values. I was also able to concatenate the 2 columns thus eliminating the 4 extra combination columns and the 1 "mid step" column using the method.

=MIN(VLOOKUP((A1&B1),Ratings!$A$1:$B$25,2,FALSE),VLOOKUP((C1&D1),Ratings!$A$1:$B$25,2,FALSE),VLOOKUP((E1&F1),Ratings!$A$1:$B$25,2,FALSE),VLOOKUP((G1&H1),Ratings!$A$1:$B$25,2,FALSE))