formula1

ageeb · May 22nd, 2002, 08:31 AM

Hi

My database has one of its column that looks like this

A

M65-M45
2M65-M45
M45-2M65

I want a formula that calculates the number of times 2M65 occured and another one to calculate the number of times M65 occures.

Thanks in advance

sann0638 · May 22nd, 2002, 08:42 AM

something like:
{=count(if(or(left(A:A,4)="2M65",right(A:A,4)="2M65"))}

it's an array formula, so do Ctrl-Shft-Entr to enter it.

IML · May 22nd, 2002, 08:44 AM

If your values can only occur once per cell (ie a value of 2M65-2m65 if possilbe, counts as 1), you could use the wildcard charater asterick in a countif, such as
=COUNTIF($A$1:$A$3,"*2m65*")
and
=COUNTIF($A$1:$A$3,"*m65*")

Chris Davison · May 24th, 2002, 01:57 PM

what's this got to do with Formula 1 ?

I propose this board delete this user for getting me excited for no apparent reason

May 22nd, 2002, 08:42 AM	#2
sann0638 Board Regular Join Date: Mar 2002 Posts: 175	something like: {=count(if(or(left(A:A,4)="2M65",right(A:A,4)="2M65"))} it's an array formula, so do Ctrl-Shft-Entr to enter it. __________________ Regards, Mike.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 22nd, 2002, 08:31 AM	#1
ageeb New Member Join Date: May 2002 Posts: 31	Hi My database has one of its column that looks like this A M65-M45 2M65-M45 M45-2M65 I want a formula that calculates the number of times 2M65 occured and another one to calculate the number of times M65 occures. Thanks in advance

May 22nd, 2002, 08:44 AM	#3
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	If your values can only occur once per cell (ie a value of 2M65-2m65 if possilbe, counts as 1), you could use the wildcard charater asterick in a countif, such as =COUNTIF($A$1:$A$3,"2m65") and =COUNTIF($A$1:$A$3,"m65")

May 24th, 2002, 01:57 PM	#4
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	what's this got to do with Formula 1 ? I propose this board delete this user for getting me excited for no apparent reason