Conditional sum formula problems in XL2000

[jecowling]

I have a database of patient information. Column B contains surgery
names, column L contains the causes of patient's heart failure.

I'm trying to use the following conditional sum formula:

=SUM(IF('All practice data'!$B$2:$B$361="BRS",IF('All practice
data'!L$2:L$361="=*AF*",1,0)))

to return the number of cells for BRS which contain the text "AF"
anywhere within them.

It returns the value '0' when it should return '1' (there is one 'BRS'
cell in column L which contains the text "MI, AF". There are no error
messages to suggest that the formula has a problem, it simply does not
pick up on the wildcard operators used in the formula above.

(I am remembering to enter the array formula using ctrl+shift+enter)

Any answers? Help much appreciated.

Cheers

Jenny



------------------------------------------------


-- View and post Excel related usenet messages directly from http://www.ExcelTip.com/forum
at http://www.ExcelTip.com/
------------------------------------------------

 

[Joe]

Try this. SUM(IF((B2:B361="BRS")*(L2:L361="AF"),1))
Should work.

-----Original Message-----
I have a database of patient information. Column B contains surgery
names, column L contains the causes of patient's heart failure.

I'm trying to use the following conditional sum formula:

=SUM(IF('All practice data'!$B$2:$B$361="BRS",IF('All practice
data'!L$2:L$361="=*AF*",1,0)))

to return the number of cells for BRS which contain the text "AF"
anywhere within them.

It returns the value '0' when it should return '1' (there is one 'BRS'
cell in column L which contains the text "MI, AF". There are no error
messages to suggest that the formula has a problem, it simply does not
pick up on the wildcard operators used in the formula above.

(I am remembering to enter the array formula using ctrl+shift+enter)

Any answers? Help much appreciated.

Cheers

Jenny

from http://www.ExcelTip.com/forumat http://www.ExcelTip.com/