Countif with conditions

[Pofski]

Hi,


i was wondering if it was possible to do a countif of a column, ignoring the ones where in another column there is a parameter.

A B

JAN CLN

JAN MOD

FEB CLN

MAR MOD

DEC MOD

MAR CLN

FEB REP

FEB MOD


In the example above, when trying to count amount of JAN, without MOD, it would have to give as a result 1.

FEB without CLN should give 2.


Thank you in advance

 

[DaveTurton]

Range A2:B9 contains your data


C2 contains the month you are looking for (i.e. JAN)


D2 contains the exclusion (i.e. MOD)

=SUMPRODUCT(--(A2:A9=C2),--(B2:B9<>D2))

 

[nazmul_muneer]

Pofski

Think your data is from A1:B9

Type D2=FEB and E2=CLN

Type/Copy the formula in F2

=COUNTIFS(A:A,D2)-COUNTIFS(A:A,D2,B:B,E2)


Regards


Muneer

 

[DaveTurton]

Ah yes, you can also use COUNTIFS, I only have excel 2003 at work so am unable to, hence my use of SUMPRODUCT instead

 

[Pofski]

The thing is, does it also work with exclusions?


The above example is to simplify it a bit, but in the actual spreadsheet i would have to give all the January's for example, but without CLN, CONSLT, E, MOD, PAINT, PAINT2 and REP, but with 30 other possibility's.


That is why i think it would be easier to do it with exclusion, in stead of inclusion.


i tried it with =COUNTIFS($A:$A,"jan",$B:$B,<>"CLN",$B:$B,<>"E",$B:$B,<>"MOD",...), but i keep getting error messages, so i don't know what i'm doing wrong.


Thank you in advance

 

[DaveTurton]

SUMPRODUCT works fine with the multiple exclusions


i.e.

=SUMPRODUCT(--(A2:A9="JAN"),--(B2:B9<>"CLN"),--(B2:B9<>"MOD"))

 

[DaveTurton]

Your error message is probably because you are referencing the whole column (A:A) rather than a range within the column

 

[DaveTurton]

You could get around it by using one row less than the entire column


A1:A65535


or


A2:A65536

 

[Pofski]

I think i got it to work


Thanks guys