Get a result based on conditions in several columns

charlie637

New Member
Joined
Jun 8, 2017
Messages
17
Trying to solve this.

What I want: Formula in column D saying Yes or No based on following rules:
If a cell in Col. B equals "Bob", then look everywhere in Col. A with the same content, for ex "A1", and if in Col C there is "17", then put "Yes" in D on same row as Bob.

Otherwise, "No", as shown in D6.

If B contains no "Bob", nothing happens (could return "No" or nothing)

A
B
C
D
E
1
A1
Bob
Yes
2
A1
Pet
17
3
A1
John
4
A2
Mike
17
5
A2
Jen
6
A2
Mary
7
A2
Bob
Yes
8
A3
John
9
A3
Jack
10
A3
Lisa

<tbody>
</tbody>
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Chrisdontm

Well-known Member
Joined
May 4, 2015
Messages
810
I believe I have two formulas to try;

First lets go with the "Bob Only" formula;

Put this in cell: D1 and copy it on down;

=IF(COUNTIF($A:$A,IF($B1="Bob",$A1,""))=COUNTIFS($A:$A,IF($B1="Bob",$A1,""),$C:$C,""),"","Yes")
______________________________________________

The other formula you may want to try is to make it more dynamic;

That is to say, you may want to use the formula with different names.

So use cell: G1 to enter in the person's name you want to use.

Then in cell: D1 enter this and copy it on down;

=IF(COUNTIF($A:$A,IF($B1=$G$1,$A1,""))=COUNTIFS($A:$A,IF($B1=$G$1,$A1,""),$C:$C,"<>"&17),"","Yes")

( With this one you could use another person's name, if you wanted to...)
 

charlie637

New Member
Joined
Jun 8, 2017
Messages
17
Thank you so much. Works great!
Now, if instead of just "Bob", I have a list of names that I want to behave like for "Bob", using either a range where all those names would be or by creating a list, how would the formula look like?
 

charlie637

New Member
Joined
Jun 8, 2017
Messages
17
One more thing.
In the first formula, I do not think it takes into consideration "17" specifically. But it needs to be. There could be other values in Col. C, so the formula needs to behave the way I described it only if there is that number 17 in Col. C.
Thanks!
 

inactiveuserps07

Banned user
Joined
May 25, 2017
Messages
748

ADVERTISEMENT

Nice, charlie637 — and quick.

Here is a shorter version:

=IF(B1="Bob",IF(COUNTIFS($A:$A,A1,$C:$C,17)>0, "YES", ""),"")

Or, if input fields for "Bob" and "17 were G1 and J1 respectively:

=IF(B1=$G$1,IF(COUNTIFS($A:$A,A1,$C:$C,$J$1)>0, "YES", ""),"")
 

inactiveuserps07

Banned user
Joined
May 25, 2017
Messages
748
Christdontm, if the names were in Column N, for instance, and the input field where you'd check for 17 was $J$1:


=IF(IFERROR(MATCH(B1,$N:$N,0),0)>0,IF(COUNTIFS($A:$A,A1,$C:$C,$J$1)>0,"YES",""),"")


That would go into D1 and be copied down the column as far as you need it.
 

inactiveuserps07

Banned user
Joined
May 25, 2017
Messages
748

ADVERTISEMENT

You're always better to have things like the name list formatted as a named table.

So let's say your name list is in N1:N10 with the heading NAME. Select N1:N10, choose Insert from the ribbon and then click "Table." Make sure the checkbox for "My table has headers" is checked. Click "OK." The tab will switch automatically to the Design tab. Rename your list to the left of the ribbon, for instance: NameList1.

Then the formula would look like this:


=IF(IFERROR(MATCH(B1,NameList1,0),0)>0,IF(COUNTIFS($A:$A,A1,$C:$C,$J$1)>0,"YES",""),"")


And if your main data set will always have people's names two columns to the left of your "YES" column, you can use this formula in the "YES" column, no matter where it starts:


=IF(IFERROR(MATCH(INDIRECT(ADDRESS(ROW(),COLUMN()-2)),NameList1,0),0)>0,IF(COUNTIFS($A:$A,A1,$C:$C,$J$1)>0,"YES",""),"")


I'd also recommend formatting your main data as a table the same way I described above, so that the formula will automatically be added as you add new records. Otherwise, you'll have to keep copying it down to the new entries.
 
Last edited:

charlie637

New Member
Joined
Jun 8, 2017
Messages
17
I actually already did what you suggested, creating a list instead :)
Thanks for following up.
 

Forum statistics

Threads
1,136,314
Messages
5,675,021
Members
419,543
Latest member
Casp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top