nested countif

[f_mayr]

hello

im trying to create a formula with two conditions

ex:
i have two collumns
A with names
B with ages

i need to sum fex. all those who are named "john" and are 23

its basically a simple IF(name==john&&age=23)

is that possible somehow?
i thougth about a nested countif. but somehow thats not working

thanks

[sweep]

Hi,

How about this:

=SUMPRODUCT((A1:A100="John")*(B1:B100=23))

Obviously change the ranges to suit your data

[deadlyduck]

To count the number of people named John who are aged 23 try the following:

Please Login or Register  to view this content.

Note that the range length must be the same in each part (a1:a20 and b1:b20)

[f_mayr]

thats what i needed thanks.

but another question. isnt it possible to paste this over the whole column?

like this:
=SUMPRODUCT(--(_23.06.2009!$G:$G="John");--(_23.06.2009!$H:$H=23))

_23.06.2009 beeing the sheet name

[sweep]

thats what i needed thanks.

but another question. isnt it possible to paste this over the whole column?

like this:
=SUMPRODUCT(--(_23.06.2009!$G:$G="John");--(_23.06.2009!$H:$H=23))

_23.06.2009 beeing the sheet name

Not until you upgrade to XL2007

[f_mayr]

crap.

thanks anyway for the answers!

[f_mayr]

ah there has just appeared a new problem

is it possible to sort on a negative term?

meaning everybody whos name is NOT john and is 23...

[sweep]

Yup,

=SUMPRODUCT(NOT(A1:A100="John")*(B1:B100=1))

[f_mayr]

edit: to slow

[f_mayr]

thanks you guys are great