CODEWITHSUNDEEP
excelexcel-formula
greystash
greystash

Reputation: 337

Excel COUNTIF Error

What I'm trying to do is create a spreadsheet that has a list of staff and clients, and tells you if a staff person has met that client before. Each staff person has a list of clients underneath their name and at the top of the spreadsheet there's two drop-down boxes where somebody can select a staff person, and a client, then under the status heading it will say whether the two have met before.

I've only been able to get the formula to process one column/staff person, but I get errors when I try to include more columns. Here's my formula for the "Status" cell;

=IF(COUNTIF(C$21:C$455,D5),"Have Met","Have Not Met")

If I add multiple IF statements to the formula e.g below I get a #VALUE! error. So I'm doing something wrong..

=IF(COUNTIF(C$21:C$455,D5),"Have Met","Have Not Met"),IF(COUNTIF(D$21:D$455,D5),"Have Met","Have Not Met")

Sorry I accidentally cut off the column letters and row numbers from the image. Staff column is "C", Client is "D" etc. Staff Header row is "20", First client row is "21" etc.

Is there a better formula I should be using? Example Image

Upvotes: 0

Views: 157

Answers (2)

QHarr
QHarr

Reputation: 84475

Here is a different way which also handles unknown staff and client. You would adjust range as appropriate.

=IF(ISERROR(MATCH(D5,INDEX($C$20:$E$26,,MATCH($C$5,$C$20:$E$20,0)),0)),"Have Not met","Have Met")

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 37155

Use Countif() function with combination of Index() and Match() function.

=IF(COUNTIF(INDEX($C$20:$E$26,,MATCH($C$5,$C$20:$E$20,0)),$D$5)>0,"Have Met","Have Not Met")

enter image description here

You may need to adjust the formula in case of you if your data in more ranges.

Upvotes: 2

Related Questions