counting rows meeting multiple criteria

  • Thread starter Thread starter Karen Keeter
  • Start date Start date
K

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.
 
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")))
 
=SUMPRODUCT(ISNUMBER(MATCH($A$2:$A$100,{"writing","to
do"},0))*($B$2:$B$100="Tom"))
 
Karen Keeter said:
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.
Click to expand...

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