counting rows meeting multiple criteria

[Karen Keeter]

I have two columns with different values and I want to
count as follows:
(If column A has the value "writing" or if column A has
the value of "to do") AND (if column B has the
value "Tom") then count this row.
So I end up with a total which is the number of rows which
have Writing in colunn A and Tom in colunmn B plus the
number of rows which have To Do in column A and Tom in
colunmn B.

I am sure (I hope) there is a way to do this, but can't
quite seem to figure it out.

 

[Debra Dalgleish]

You can use the SUMPRODUCT function to count the instances. For example:

=SUMPRODUCT((OR($A$2:$A$20="writing",$A$2:$A$20="to
do")*($B$2:$B$20="Tom")))

 

[Karen Keeter]

Thanks Debra! I will try that

-----Original Message-----
You can use the SUMPRODUCT function to count the instances. For example:

=SUMPRODUCT((OR($A$2:$A$20="writing",$A$2:$A$20="to
do")*($B$2:$B$20="Tom")))





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.

 

[Aladin Akyurek]

=SUMPRODUCT(ISNUMBER(MATCH($A$2:$A$100,{"writing","to
do"},0))*($B$2:$B$100="Tom"))

 

[Paul]

I have two columns with different values and I want to
count as follows:
(If column A has the value "writing" or if column A has
the value of "to do") AND (if column B has the
value "Tom") then count this row.
So I end up with a total which is the number of rows which
have Writing in colunn A and Tom in colunmn B plus the
number of rows which have To Do in column A and Tom in
colunmn B.

I am sure (I hope) there is a way to do this, but can't
quite seem to figure it out.

Try this, with the ranges adjusted to suit (you cannot use whole column
references):
=SUMPRODUCT((A1:A100={"writing","to do"})*(B1:B100="Tom"))