countif

[Sharkie21]

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.

 

[NBVC]

Countif doesn't work with more than 1 criteria...

try:

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

 

[Richard Schollar]

Hi

You need another formula such as SUmproduct:

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

 

[ADAMC]

Hi

Try SUMPRODUCT:

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

SYNTAX IS:

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

 

[Sharkie21]

Thanks.

 

[Sharkie21]

What about something like this

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]

Maybe:

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]

Nice it works. What does SIGN do?

 

[Richard Schollar]

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]

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.