COUNTIFS workaround in Excel 2003

Joined: May 03, 2007 Posts: 1

I need help. I have two columns I am working with - X and P. there are number values in both. I need to see how many times X=1 AND P=1 in the entire column. COUNTIFS worked with the 2007 edition my husband has at his work, but everyone here has 2003 and COUNTIFS does not work in 2003. Does any one have any suggestions on how to get this? Thanks!

Krista

Joined: May 05, 2008 Posts: 1

you can get around this with a sum array formula:

={sum(if(x=1,if(p=1,1,0),0))}

so if x is in the cells A2:A10 and p is in the cells B2:B10 then you would enter the formula

=sum(if(A2:A10=1,if(B2:B10=1,1,0),0))

and then hit control-shift-enter so that excel calculates it as an array formula. what this does is create a virtual column which has the value 1 in rows where x=1 and p=1, but has the value 0 in rows where either x or p is not 1, and then adds this virtual column up.