# Question on COUNTIFS with a nested "AND"

#### kampffexcel

##### New Member
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!

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Try:
Code:

Try:
Code:

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!

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

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

Of course, I knew that!!

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

Replies
2
Views
287
Replies
8
Views
395
Replies
2
Views
167
Replies
3
Views
621
Replies
1
Views
235

1,207,203
Messages
6,077,032
Members
446,252
Latest member
vettaforza

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back