Solved Countifs and Countif and AND

Microsoft Excel 010 - complete package
May 16, 2013 at 06:31:24
Specs: Windows XP
Why won't this formula work???
***=IF(COUNTIFS(Data!E:E,A4),"MMN","")+AND(IF(COUNTIFS('Metro South'!E:E,A4),"MSNJ",""))+AND(IF(COUNTIFS('West Coast'!E:E,A4),"WC",""))))

Watching the Evaluate formula, it picks up the first response as 'MMN', then continues and comes back with #Value for the next two responses instead of the blank. I've tried it without the + before the And (which Excel added) and it put in an asterick instead. I've also tried it with a Countif.

Help, please!

See More: Countifs and Countif and AND

Report •

May 16, 2013 at 12:39:48
You can return only ONE answer,
either MMN, or MSNJ or WC

So which answer to you want to give?

You could do a nested IF, something like:

=IF(COUNTIFS(Data!E:E,A4),"MMN",IF(COUNTIFS('Metro South'!E:E,A4),"MSNJ",IF(COUNTIFS('West Coast'!E:E,A4),"WC",""))))

Without knowing what it is your trying to do, not much more to offer.


Report •

May 16, 2013 at 19:13:15
✔ Best Answer
While I'll agree that we can't be much help without knowing what GingerLeake is trying to do, I don't necessarily agree that she can only return one answer.

If she is trying to use the plus signs and/or the AND function to return multiple answers, she should be using the Concatenation operator, &.

For example, this will return 23:


P.S. I don't think that that is what she is trying to do, I'm merely pointing out that it's possible to return multiple answers since she is performing her IF's across multiple sheets. If the value in A4 is found in Column E of all three sheets, it's possible to return MMNMSNJWC with the &.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

May 17, 2013 at 05:34:49
I understand that, the reason I did not mention it, is it would end up a very complicated formula:

Something like this will return all three strings concatenated in one cell:


But it works only if the First COUNTIFS returns True,
else you get a False string returned.

I did not try to account for all combinations of strings.
A bit more then I wish to attempt just now seeing as the OP
has not replied.


Report •

Related Solutions

May 17, 2013 at 05:54:34
I was really hoping someone would say, "Oh you have too many ( or the ' is in the wrong place!" Ah well.

And what I'm trying to do is check for an employee's ID (A4) but on all of the regional sheets (Data, Metro South, West Coast) and if found, show the regional code (MMN, MSNJ, WC.) This is on a worksheet in the same workbook called 'Reports' and it's for management to keep track of where the employee is picking up cases from.

It IS possible that an employee could be on all three sheets so I'll try it with the & that DerbyDad suggested and will let you know what happens.


Report •

May 17, 2013 at 06:44:48
Try this:

=IF(COUNTIF(Data!E:E,A4),"MMN","")&IF(COUNTIF('Metro South'!E:E,A4),"MSNJ","")&IF(COUNTIF('West Coast'!E:E,A4),"WC","")

Just concatenated your three =IF() functions.


Report •

Ask Question