# Counti if multiple Criteia is met (if, and, countif together possible?)

#### laxjuan05

##### New Member
Hello,
I am trying to figure out a way to get a count if certain criteria's are met including an AND function. Is this possible? Example: I am trying to get a count if Column B & C both do not equal "0", Column A equals "GC", and Column D equals "Active". Is this possible? I currently tried IF(AND(B:B<>0,C:C<>0),COUNTIFS(A:A,"GC",D:D,"Active"))

Outcome should be "2"

 Group # of A Drawing # of X Drawing Status GC 0 0 Active PC 2 1 Complete AV 0 6 Active GC 6 4 Complete SI 4 0 Complete SI 5 8 Active GC 5 0 Active GC 1 1 Active

<tbody>
</tbody>

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### T. Valko

##### Well-known Member
I think you want this...

=COUNTIFS(A:A,"GC",B:B,"<>0",C:C,"<>0",D:D,"Active")

#### laxjuan05

##### New Member
Hello T. Valko,

Thanks for the response. I tried this before, however, when I insert =COUNTIFS(A:A,"GC",B:B,"<>0",C:C,"<>0",D:D,"Active") I get a count of 1 instead of 2. It is not counting row 8 because there is a 0 zero in column C and a 5 in column B. I want it to count row 8 because Column B does have a value of 5. Only don't count it if there is a 0 in column B and C.
Is this correct? Make sense?
 Group # of A Drawing # of X Drawing Status GC 0 0 Active PC 2 1 Complete AV 0 6 Active GC 6 4 Complete SI 4 0 Complete SI 5 8 Active GC 5 0 Active GC 1 1 Active

<tbody>
</tbody>

#### laxjuan05

##### New Member
I think you want this...

=COUNTIFS(A:A,"GC",B:B,"<>0",C:C,"<>0",D:D,"Active")

Hello T. Valko,

Thanks for the response. I tried this before, however, when I insert =COUNTIFS(A:A,"GC",B:B,"<>0",C:C,"<>0",D:D,"Active") I get a count of 1 instead of 2. It is not counting row 8 because there is a 0 zero in column C and a 5 in column B. I want it to count row 8 because Column B does have a value of 5. Only don't count it if there is a 0 in column B and C.
Is this correct? Make sense?
 Group # of A Drawing # of X Drawing Status GC 0 0 Active PC 2 1 Complete AV 0 6 Active GC 6 4 Complete SI 4 0 Complete SI 5 8 Active GC 5 0 Active GC 1 1 Active

<tbody>
</tbody>

#### T. Valko

##### Well-known Member
Try this version...

=SUMPRODUCT(--(A2:A9="GC"),SIGN((B2:B9<>0)+(C2:C9<>0)),--(D2:D9="Active"))

#### laxjuan05

##### New Member
Try this version...

=SUMPRODUCT(--(A2:A9="GC"),SIGN((B2:B9<>0)+(C2:C9<>0)),--(D2:D9="Active"))

This worked, perfect! Thank you for all your help!
If you have a chance could I ask you one more thing, continuing on the same example but also add the criteria for color, count if Column E does not contain blue or green. Is this possible, adding an OR function also?

#### laxjuan05

##### New Member
Try this version...

=SUMPRODUCT(--(A2:A9="GC"),SIGN((B2:B9<>0)+(C2:C9<>0)),--(D2:D9="Active"))

Sorry, forgot to include the data set in the previous response. Is it possible to add another OR function to see if criteria is met. I would like to add the criteria, if the color is blue or green then don't count.

Group# of A Drawing# of X DrawingStatusColor
GC00
 Active

<colgroup><col></colgroup><tbody>
</tbody>
 Red, Blue, Green

<colgroup><col></colgroup><tbody>
</tbody>
PC21
 Complete

<colgroup><col></colgroup><tbody>
</tbody>
 Red, Blue

<colgroup><col></colgroup><tbody>
</tbody>
AV06
 Active

<colgroup><col></colgroup><tbody>
</tbody>
 Purple, Green, Blue

<colgroup><col></colgroup><tbody>
</tbody>
GC64
 Complete

<colgroup><col></colgroup><tbody>
</tbody>
 Purple, Red

<colgroup><col></colgroup><tbody>
</tbody>
SI40
 Complete

<colgroup><col></colgroup><tbody>
</tbody>
 Red, Purple

<colgroup><col></colgroup><tbody>
</tbody>
SI58
 Active

<colgroup><col></colgroup><tbody>
</tbody>
 Blue

<colgroup><col></colgroup><tbody>
</tbody>
GC50
 Active

<colgroup><col></colgroup><tbody>
</tbody>
 Red, Purple

<colgroup><col></colgroup><tbody>
</tbody>
GC11
 Active

<colgroup><col></colgroup><tbody>
</tbody>
 Purple

<colgroup><col></colgroup><tbody>
</tbody>
GC02
 Active

<colgroup><col></colgroup><tbody>
</tbody>
 Blue, Red

<colgroup><col></colgroup><tbody>
</tbody>
GC00
 Active

<colgroup><col></colgroup><tbody>
</tbody>
 Red, Blue, Green

<colgroup><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>

#### T. Valko

##### Well-known Member

Data Range
 A​ B​ C​ D​ E​ 1​ Group​ A Draw​ X Draw​ Status​ Color​ 2​ GC​ 0​ 0​ Active​ Red, Blue, Green​ 3​ PC​ 2​ 1​ Complete​ Red, Blue​ 4​ AV​ 0​ 6​ Active​ Purple, Green, Blue​ 5​ GC​ 6​ 4​ Complete​ Purple, Red​ 6​ SI​ 4​ 0​ Complete​ Red, Purple​ 7​ SI​ 5​ 8​ Active​ Blue​ 8​ GC​ 5​ 0​ Active​ Red, Purple​ 9​ GC​ 1​ 1​ Active​ Purple​ 10​ GC​ 0​ 2​ Active​ Blue, Red​ 11​ GC​ 0​ 0​ Active​ Red, Blue, Green​

Tell us what ROWS meet ALL the conditions and should be counted.

Replies
3
Views
257
Replies
3
Views
382
Replies
6
Views
142
Replies
6
Views
113
Replies
9
Views
328

1,191,002
Messages
5,984,100
Members
439,872
Latest member
noaman79

### 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