CODEWITHSUNDEEP
excelcountif
Meeeee
Meeeee

Reputation:

Multiple conditions in excel 2002

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.

Upvotes: 0

Views: 2479

Answers (4)

jtolle
jtolle

Reputation: 7124

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:

https://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.

Upvotes: 2

Robert Mearns
Robert Mearns

Reputation: 11996

The DCOUNT function allows for multiple criteria.

The formula would look like this

=DCOUNT(A2:B8,"ColumnA",A11:B12)

if you have your criteria below the data.

Screenshot of data and formula http://img31.imageshack.us/img31/1093/dcount.png

Upvotes: 0

Vincent De Smet
Vincent De Smet

Reputation: 4979

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

Upvotes: 0

Gary McGill
Gary McGill

Reputation: 27556

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.

Upvotes: 1

Related Questions