# countif

#### Sharkie21

##### Active Member
Can someone tell me why this is not working?

=COUNTIF(Data!O3:O2126,AND(Data!O3:O2126="POP",Data!N3:N2126="COKE"))

so if I have

O N
POP COKE
POP COLA
POP PEPSI

to give me 1.

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### NBVC

##### Well-known Member
Countif doesn't work with more than 1 criteria...

try:

=Sumproduct(--(Data!O3:O2126="POP"),--(Data!N3:N2126="COKE"))

#### Richard Schollar

##### MrExcel MVP
Hi

You need another formula such as SUmproduct:

Code:
``=SUMPRODUCT((Data!O3:O2126="POP")+0,(Data!N3:N2126="COKE")+0)``

##### Well-known Member
Hi

Try SUMPRODUCT:

=SUMPRODUCT(--(O3:O2126="pop"),--(N3:N2126="coke"))

SYNTAX IS:

=sumproduct(--(range1=contidtion1),--(range2=condition2))

Thanks.

#### Sharkie21

##### Active Member

SUMPRODUCT(--(Data!\$O\$3:\$O\$2126="POP"),--(Data!\$N\$3:\$N\$2126="COKE"),--OR(Data!\$W\$3:\$W\$2126="Y",Data!\$W\$3:\$W\$2126="D)")

O N W
POP COKE Y
POP COKE D
POP COKE E

I want 2.

#### Richard Schollar

##### MrExcel MVP
Maybe:

Code:
``SUMPRODUCT(--(Data!\$O\$3:\$O\$2126="POP"),--(Data!\$N\$3:\$N\$2126="COKE"),SIGN((Data!\$W\$3:\$W\$2126="Y")+(Data!\$W\$3:\$W\$2126="D")))``

#### Sharkie21

##### Active Member
Nice it works. What does SIGN do?

#### Richard Schollar

##### MrExcel MVP
Returns 0 if argument is 0, 1 if argument is positive (ie >0) - ---- also returns -1 if argument is negative, but that won't happen in this formula.

#### Diffy

##### Well-known Member
Richard uses a function call rather than a "--" because when performing the OR operation, you need to use "+". By using a function we avoid typing "+--".

You could use the N() functino as well.

Replies
1
Views
209
Replies
10
Views
339
Replies
11
Views
372
Replies
8
Views
184
Replies
1
Views
154

1,191,092
Messages
5,984,604
Members
439,896
Latest member
SquareCare

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