I need to count items in one column when the word active appears in another
column.

What I am trying to do is count all members who are "active" and who have a
shunt. Active and Shunt are in different columns and I know that I could just
filter the sheet by the columns, but I was hoping for an easier method. By
developing a formula.

=sumproduct((a2:a22="active")*(b2:b22="shunt"))

--
Don Guillett
SalesAid Software

"Bill Roberts" wrote in message
...
I need to count items in one column when the word active appears in another
column.

What I am trying to do is count all members who are "active" and who have
a
shunt. Active and Shunt are in different columns and I know that I could
just
filter the sheet by the columns, but I was hoping for an easier method. By
developing a formula.

One way:

=SUMPRODUCT(--(A1:A1000="active"),--(B1:B1000="shunt"))

See http://www.mcgimpsey.com/excel/doubleneg.html for an explanation of
the "--"

You might also consider using a Pivot Table, if you have multiple items
to count.


In article ,
Bill Roberts wrote:

I need to count items in one column when the word active appears in another
column.

What I am trying to do is count all members who are "active" and who have a
shunt. Active and Shunt are in different columns and I know that I could just
filter the sheet by the columns, but I was hoping for an easier method. By
developing a formula.

These formulas wanted to sum the two columns or multiply them. I am wanting
to onlu sum one column if the other has a particular text in it.

"Bill Roberts" wrote:

I need to count items in one column when the word active appears in another
column.

What I am trying to do is count all members who are "active" and who have a
shunt. Active and Shunt are in different columns and I know that I could just
filter the sheet by the columns, but I was hoping for an easier method. By
developing a formula.

Did you actually try it?

I suspect not.

In article ,
Bill Roberts wrote:

These formulas wanted to sum the two columns or multiply them. I am wanting
to onlu sum one column if the other has a particular text in it.

Both fromulas work to COUNT, not SUM if BOTH columns meet the criteria on
the SAME row.

--
Don Guillett
SalesAid Software

"Bill Roberts" wrote in message
...
These formulas wanted to sum the two columns or multiply them. I am
wanting
to onlu sum one column if the other has a particular text in it.

"Bill Roberts" wrote:

I need to count items in one column when the word active appears in
another
column.

What I am trying to do is count all members who are "active" and who have
a
shunt. Active and Shunt are in different columns and I know that I could
just
filter the sheet by the columns, but I was hoping for an easier method.
By
developing a formula.