IF statement to return different words for condition A, condition B, or both A and B.

[Worksong]

Hello! I am struggling to write an IF statement that will return different text depending on whether one of two conditions is met, or both conditions are met. The worksheet tallies adult and childhood ADHD symptoms from two different categories - inattentive and hyperactive/impulsive; I'm only interested in the adult totals for the formulae.

Condition A: Five or more adult inattentive symptoms - return "Inattentive"
Condition B: Five or more adult hyperactive/impulsive symptoms - return "Hyperactive"
Condition C: Conditions A and B both met - return "Combined"
No condition met - return a blank cell (not FALSE).

So far I've been able to make functional IF statements that correctly return "Inattentive" or "Hyperactive", and one that correctly returns "Combined", but not one that correctly combines the two.

The Inattentive symptom tally is in cell D17
The Hyperactive/Impulsive symptom tally is in cell D31

Here are the formulas I've tried so far:
=IF(AND(D17>=5,D31>=5),”Combined”,IF(D17>=5,”Inattentive”,IF(D31>=5,”Hyperactive”)))
=IF(D17>=5,”Inattentive”,IF(AND(D31>=5,”Hyperactive”,IF(AND(D17>=5,D31>=5),”Combined”)))

This formula correctly returns the "one or the other" condition:
=IF(D17>=5,"Inattentive",IF(D31>=5,"Hyperactive"))

This one correctly returns the "both" condition:
=IF(AND(D17>=5, D31>=5), "Combined","")

Any suggestions are appreciated!
David

Here's the whole worksheet:

Copy of ADHD Questionnaires Scoring Spreadsheet Draft 041824.xlsm
	I
32
DIVA-5

 

[etaf]

what happens with this formula - all i have added is "" blank so if none are met you get a blank and not false

=IF(AND(D17>=5,D31>=5),”Combined”,IF(D17>=5,”Inattentive”,IF(D31>=5,”Hyperactive”,"")))

 

[Worksong]

Hi Wayne, thanks for the reply. With that formula, if none of the conditions are met, a blank cell is indeed returned. Any condition met returns "#NAME?"

 

[Sergius]

Any condition met returns "#NAME?"

The name of the functions does not match your office locale (language).

 

[Fluff]

You need to change the quotes from smart quotes (ie ”) to normal quotes (ie ")

 

[Worksong]

You need to change the quotes from smart quotes (ie ”) to normal quotes (ie ")

Problem solved, y'all. Thanks to you, @Sergius , and @etaf ! So the style of quotes is what was tripping me up this whole time … sheesh. Picky picky picky, Excel.

I'm not sure who to mark as having the solution and it won't let me select all three, so I'm going with @etaf because he provided the formula, but input from all three of you helped.

David

 

[Fluff]

Glad we could help & thanks for the feedback.