-1

I have a formula:

IF(AND(COUNTIFS(A:A,A1,B:B,"Bob")+COUNTIFS(A:A,A1,C:C,17)>=2,C1=17),B1,"")

that does the following:

  • If "Bob" is in Col B AND there is a "17" in Col C while still having a1 in Col A
  • Then retrieve data from Col B on same row as "17", in this case B2.

So for the series of rows that have a2 in Col A, the conditions are true again because there is a "Bob" in Col B associated with a2, and a "17" in Col C associated with a2, so "Mike" is retrieved in Col D.

For the series of rows that have a3 in Col A, conditions are not true, as there is no "17" in Col C, so Col D remains empty.

For the series of rows that have a4 in Col A, conditions are not true, as there is no "Bob" in Col B, so Col D remains empty.

Now, I would like to change "Bob" (text string) for a Named Range.

However, though COUNTIF works with Named Ranges, it does not with COUNTIFS.

Is there a way to do this?

jeffbyrnes
  • 2,182
  • 2
  • 22
  • 33
charlie637
  • 1

1 Answers1

0

With:

enter image description here

This formula:

=COUNTIFS(A1:A10,"=" & A1,B1:B10,"=Bob")

naturally returns 3

Next we enter Bob in cell G11 and assign G11 the Name James

We find:

=COUNTIFS(A1:A10,"=" & A1,B1:B10,"=" & james)

also returns 3

enter image description here

This:

 =COUNTIFS(A1:A10,"=" & A1,B1:B10, james)

also works.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Hello, Thank you for reviewing my post. I realized the image I had uploaded is not showing as I do not have the 10 required points of "reputation". And the screenshots was essential to understand my question. The behavior of the formula is a bit more complex. – charlie637 Jun 23 '17 at 15:06