Nested IF

[smithomatic]

Hello,

I was wandering if someone here would be able to help me write a nested IF/AND/OR statement or something that will perform a similar job.

I need Cell A1 to say "Yes" if Cell A3 has any of the following names:

Laura
Jade
Rebecca
Sarah
Zoe
Elaine
Sue

 

[T. Valko]

Hello,

I was wandering if someone here would be able to help me write a nested IF/AND/OR statement or something that will perform a similar job.

I need Cell A1 to say "Yes" if Cell A3 has any of the following names:

Laura
Jade
Rebecca
Sarah
Zoe
Elaine
Sue

Put those names in a range of cells like E1:E7.

Then:

=IF(COUNTIF(E1:E7,A3),"Yes","No")

 

[VoG]

Try

=IF(ISNUMBER(MATCH(A3,{"Laura","Jade","Rebecca","Sarah","Zoe","Elaine","Sue"},0)),"Yes","")

 

[smithomatic]

perfect! thank you very much, i tried a few different things but didnt use the countif idea

 

[T. Valko]

perfect! thank you very much, i tried a few different things but didnt use the countif idea

You're welcome. Thanks for the feedback!

 

[taurean]

Another approach,
=IF(A3<>"",IF(ISNUMBER(FIND(A3,"LauraJadeRebeccaSarahZoeElaineSue",1)),"YES","NO"),"")

 

[smithomatic]

I actually did have one more problem I need solving, little hard to explain but here goes;

Ok so I have one column that contains the names of the people I posted previously and another column that contains a number. I need to count the total that each person has next to thier name, E.G:

Column A Column B

Laura 60
Laura 20

Jade 60
Rebecca 30
Sarah 20

Laura 20


So from this we can see that in Total Laura has 100, Jade 60, rebecca 30 and sarah 20. I need a formula that can count these totals. *The columns do containt random spaces, as I have tried to demonstrate.

Im thinking this will probably require some kind of Vlookup but il leave it to you experts to decide my best route.

Many thanks for all your posts so far

 

[VoG]

Try

=SUMIF(A:A,"Laura",B:B)

 

[T. Valko]

I actually did have one more problem I need solving, little hard to explain but here goes;

Ok so I have one column that contains the names of the people I posted previously and another column that contains a number. I need to count the total that each person has next to thier name, E.G:

Column A Column B

Laura 60
Laura 20

Jade 60
Rebecca 30
Sarah 20

Laura 20


So from this we can see that in Total Laura has 100, Jade 60, rebecca 30 and sarah 20. I need a formula that can count these totals. *The columns do containt random spaces, as I have tried to demonstrate.

Im thinking this will probably require some kind of Vlookup but il leave it to you experts to decide my best route.

Many thanks for all your posts so far

Try this...

Book1
	A	B	C	D	E
2	Laura	60	_	Laura	100
3	Laura	20	_	Jade	60
4	_	_	_	Rebecca	30
5	Jade	60	_	Sarah	20
6	Rebecca	30	_	_	_
7	Sarah	20	_	_	_
8	_	_	_	_	_
9	Laura	20	_	_	_
Sheet1

This formula entered in E2 and copied down:

=SUMIF(A$2:A$9,D2,B$2:B$9)