Multiple conditions in excel 2002

Question

Hello,

I am using excel 2002 to create a spreadsheet. What I am trying to do is use the countif function but I have more than one condition. So I have 2 columns with a list of numbers and what I want to say is count the number of occurences where the number x is in one column and the number y is in the other column (in the same row). E.g.

1   1
1   1
1   2
2   2
2   3
3   3

So if in the above I wanted to count the rows where the first column had the number 1 and the second column had the number 2, the answer should be 1.

I can't use the COUNTIF function because that only allows you to specify one condition and the COUNTIFS isn't available because I am using excel 2002.

Please Help.

Answer 1

You could create a 3rd column which joins the two other column values with (say) a space or other special character, and then test for the combined value with COUNTIF.

If you don't want to add another column on your worksheet, you could put it on a different worksheet - or even create a dynamic named range.

Answer 2

Personally I love the SUMPRODUCT function but I'm not sure if you have it in Excel2002

Here's a very good resource for multiple conditions: http://www.ozgrid.com/Excel/sum-if.htm

Answer 3

This is a job for an array formula. In your case you can do:

=SUM((A1:A6=1)*(B1:B6=2))

entered as an array forumla (Ctrl-Shift-Enter)

The equality tests each return an array of boolean values, and the multiplication does an element-wise multiply (so a logical AND in this case). SUM coerces booleans to numbers when it adds up the resulting array.

EDIT: In an answer to this similar question:

http://stackoverflow.com/questions/576569/use-2-conditions-in-excel-sumif

Jon Fournier posted a link to:

http://www.cpearson.com/excel/ArrayFormulas.aspx

which has a lot more detail on this sort of thing.