Question on COUNTIFS with a nested "AND"

[kampffexcel]

Hi,

I'm new to this forum and thought I would post a question for a common problem I come across with my data analytics in Excel. What I am trying to do is use a COUNTIFS formula to count the number of opportunities in a sales funnel based on certain criteria. Obviously, a COUNTIFS allows me to select multiple criteria from different columns in my data. I also understand that if I have multiple criteria in the same column, I can use two sumifs together (ex: COUNTIFS(A:A,"Maybe") + COUNTIFS(A:A,"Yes") ).

However, one problem I haven't been able to solve is building a COUNTIFS with a nested AND statement for one of the columns. I'll be specific with my example:

I have to build a COUNIFS for all opportunities that contain "Lead" in the name, but that DO NOT begin with "TECH" or "MKTG".

In theory, I started building it as such COUNTIFS(A:A,AND("*Lead*","<>MKTG","<>TECH")), but I quickly realized you can't just add an add as a criteria in the COUNTIFS.

Any suggestions or thoughts on how I'd approach this in the simplest way?

Any help would be greatly appreciated. Thanks!

 

[RonB1111]

Try:

=COUNTIFS(A:A,"*Lead*",A:A,"<>"&"MKTG*",A:A,"<>"&"TECH*")

 

[Cosmic Wizard]

Try:

=COUNTIFS(A:A,"*Lead*",A:A,"<>"&"MKTG*",A:A,"<>"&"TECH*")

Hi Ron, just a quick question please?
What would be the formula if the cell didn't begin "MKTG" and "TECH", but those text string were in the cell's name?
i.e. count all cell with "Lead" but not with "MKTG*" and "TECH* regardless of their position, thankyou!

 

[RonB1111]

Cosmic Wizard,
Use an asterisk to precede those terms, like this:

=COUNTIFS(A:A,"*Lead*",A:A,"<>"&"*MKTG*",A:A,"<>"&"*TECH*")

 

[Cosmic Wizard]

Cosmic Wizard,
Use an asterisk to precede those terms, like this:

=COUNTIFS(A:A,"*Lead*",A:A,"<>"&"*MKTG*",A:A,"<>"&"*TECH*")

Of course, I knew that!!

Thanks anyway RonB1111, a big help. Sometimes you need someone to point out the very obvious!