Count

[hardeep.kanwar]

Hi

I have List of Numbers some have Repeated

Now , i want to count the Numbers but not Including No. 40

Excel Workbook
	A	B	C	D
1	Nos	*	*	Expected Result Should Be
2	120	*	*	19
3	90	*	*	*
4	90	*	*	*
5	80	*	*	*
6	80	*	*	*
7	60	*	*	*
8	60	*	*	*
9	60	*	*	*
10	50	*	*	*
11	50	*	*	*
12	50	*	*	*
13	50	*	*	*
14	40	*	*	*
15	40	*	*	*
16	30	*	*	*
17	30	*	*	*
18	30	*	*	*
19	20	*	*	*
20	20	*	*	*
21	20	*	*	*
22	10	*	*	*
Sheet1

How can i Do this Using Formula?

Regards

 

[Michael M]

=countif(a2:a22,"<>40")

 

[Peter_SSs]

Try

=COUNTIF(A2:A22,"<>40")

 

[hardeep.kanwar]

Thnx both of You


1 More Question, What if the Criteria is more than one

Means , 40 & 50

 

[Peter_SSs]

Try

=COUNTIFS(A2:A22,"<>40",A2:A22,"<>50")

 

[Michael M]

try...if you are using 2003 or older, as Peter has covered 07 and newer

=SUMPRODUCT(--(A2:A22<>40),--(A2:A22<>50))

 

[Peter_SSs]

Peter has covered 07 and newer

I only did that because Hardeep's signature says he is using 2007 (- and I finally beat you to an answer today)

 

[hardeep.kanwar]

try...if you are using 2003 or older, as Peter has covered 07 and newer

=SUMPRODUCT(--(A2:A22<>40),--(A2:A22<>50))

First of All, Thanks a Lot Both of You

Yes, Michael m Using 2003 in Office

I Apologies for not Mentioned the Version

Thanks Again

 

[Michael M]

Yep, beat me to an answer.....and can read posts better than me too.....LOL !!

 

[Peter_SSs]

Yes, Michael m Using 2003 in Office

In that case you should update your signature.