Hello,

How can I do some thing like this:

Count an occurance if value in col. A is "LifeMember" and value in Column E
is "Y" for a given range ? for instance A1:A100 and E1:E100 ?

I am trying COUNTIF but dont know how do I count only if BOTH conditions are
true?


--
Thanks !

=SUMPRODUCT(--(A1:A100="LifeMember")*(E1:E100="Y"))

"kshah_us" wrote:

Hello,

How can I do some thing like this:

Count an occurance if value in col. A is "LifeMember" and value in Column E
is "Y" for a given range ? for instance A1:A100 and E1:E100 ?

I am trying COUNTIF but dont know how do I count only if BOTH conditions are
true?


--
Thanks !

hi
countif in for 1 criteria. for more than one, might be better to use
sumproduct.
=sumproduct(--(A1:A100="lifemenber")*--(E1:E100="Y"))

Regards
FSt1

"kshah_us" wrote:

Hello,

How can I do some thing like this:

Count an occurance if value in col. A is "LifeMember" and value in Column E
is "Y" for a given range ? for instance A1:A100 and E1:E100 ?

I am trying COUNTIF but dont know how do I count only if BOTH conditions are
true?


--
Thanks !

Well, I suggest you to use macro instead
1.Right click on the toolbar select control box
2. From the control box that appears on the sheet, select and draw a
control box on the sheet
3.Double click the command button to open code window and paste
following codes

Code:
--------------------

Private Sub CommandButton1_Click()
Dim row As Integer, col As Integer
row = 1
col = 1

Dim count As Integer
count = 0
While Sheet1.Cells(row, col).Value < ""
If Sheet1.Cells(row, col).Value = "LifeMember" And Sheet1.Cells(row, col + 4).Value = "Y" Then
count = count + 1
End If
row = row + 1
Wend

MsgBox "Count=" & count
End Sub

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



Chris
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com




--
Chris Bode

Are you sure that you need the double unary minus when you are multiplying?
--
David Biddulph

"Pepito Delaosamayor" wrote
in message ...
=SUMPRODUCT(--(A1:A100="LifeMember")*(E1:E100="Y"))

"kshah_us" wrote:

Hello,

How can I do some thing like this:

Count an occurance if value in col. A is "LifeMember" and value in Column
E
is "Y" for a given range ? for instance A1:A100 and E1:E100 ?

I am trying COUNTIF but dont know how do I count only if BOTH conditions
are
true?


--
Thanks !

Are you sure that you need the double unary minuses when you are
multiplying?
They would, of course, have been needed if it were a comma instead of the
multiplying *
--
David Biddulph

"FSt1" wrote in message
...
hi
countif in for 1 criteria. for more than one, might be better to use
sumproduct.
=sumproduct(--(A1:A100="lifemenber")*--(E1:E100="Y"))

Regards
FSt1

"kshah_us" wrote:

Hello,

How can I do some thing like this:

Count an occurance if value in col. A is "LifeMember" and value in Column
E
is "Y" for a given range ? for instance A1:A100 and E1:E100 ?

I am trying COUNTIF but dont know how do I count only if BOTH conditions
are
true?


--
Thanks !