counting occurences based on two criteria

[nackington]

I have two columns of data; A and B from 2 to 700. I need to count
occurences where two criteria are satisfied... ie where the value in column A
and the value in column B is true I need to count 1 and so on, displaying a
cumulative total of the number of counts where those two criteria are true.
Any help appreciated. Thanks.

 

[Bob Phillips]

=sumproduct(--(A2:A700="value 1"),--(B2:B700="value 2"))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[nackington]

Sorry Bob, the first column is a list of usernames ie angeladalton, the
second is a list of numeric values from 0 to 7. Sumproduct ignores
non-numeric values doesn't it?

Any other suggestion? Thanks again.
Phil

 

[ivan.raiminius]

Hi Phil,

you can use this array formula (insert using ctrl+shift+enter):

=sum(if(and(criteria1,criteria2),b2:b700,0)

criteria1 is for example a2:a700="username"

Regards,
Ivan

 

[Bob Phillips]

Not the way I am using it

=sumproduct(--(A2:A700="angeladalton"),--(B2:B700=7))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[nackington]

Thanks Bob, you're quite right... it works perfectly...... Thanks v much

 

[nackington]

Thanks Ivan