Countif Function -Nested
 

I'm trying to create a countif function with three criteria, but for the life
of me I can't figure out the nested function. For example, I want to countif
column A=1, and column b=2 and column c=3. Can anyone help me with this?

Thank you so much!

try
=sumproduct((a2:a200=1)*(b2:b200=2)*(c2:c200=3))

--
Don Guillett
SalesAid Software

"Angi" wrote in message
...
I'm trying to create a countif function with three criteria, but for the
life
of me I can't figure out the nested function. For example, I want to
countif
column A=1, and column b=2 and column c=3. Can anyone help me with this?

Thank you so much!

Countif does not work well with multiple criteria.
the sumproduct function would work better try
=sumproduct(--(a1:a100=1),--(b1:b100=2),--(c1:c100=3))

"Angi" wrote:

I'm trying to create a countif function with three criteria, but for the life
of me I can't figure out the nested function. For example, I want to countif
column A=1, and column b=2 and column c=3. Can anyone help me with this?

Thank you so much!

BJ,

Using this function, gave me a response of Zero "0" and I'm sure that isn't
correct. Should I use the CTRL ALT ENTER?

Ang

"bj" wrote:

Countif does not work well with multiple criteria.
the sumproduct function would work better try
=sumproduct(--(a1:a100=1),--(b1:b100=2),--(c1:c100=3))

"Angi" wrote:

I'm trying to create a countif function with three criteria, but for the life
of me I can't figure out the nested function. For example, I want to countif
column A=1, and column b=2 and column c=3. Can anyone help me with this?

Thank you so much!

no sumproduct is an array function.
if you have the 1,2 3 combo in a row it should have worked (It did on mine)

are you sure that the 1,2 and 3 are numbers and not text?
you can check by seting up an equation such as
=(a1=1)
if it is a number 1 the answer will be true. if it is a text 1 the answer
will be false.
if there are text numbers one common way to change them to numbers is to put
a 1 someplace copy it and select what you want to convert and paste special
multiply.


"Angi" wrote:

BJ,

Using this function, gave me a response of Zero "0" and I'm sure that isn't
correct. Should I use the CTRL ALT ENTER?

Ang

"bj" wrote:

Countif does not work well with multiple criteria.
the sumproduct function would work better try
=sumproduct(--(a1:a100=1),--(b1:b100=2),--(c1:c100=3))

"Angi" wrote:

I'm trying to create a countif function with three criteria, but for the life
of me I can't figure out the nested function. For example, I want to countif
column A=1, and column b=2 and column c=3. Can anyone help me with this?

Thank you so much!

It worked on two columns, but the third column is a date and it isn't working.



"bj" wrote:

no sumproduct is an array function.
if you have the 1,2 3 combo in a row it should have worked (It did on mine)

are you sure that the 1,2 and 3 are numbers and not text?
you can check by seting up an equation such as
=(a1=1)
if it is a number 1 the answer will be true. if it is a text 1 the answer
will be false.
if there are text numbers one common way to change them to numbers is to put
a 1 someplace copy it and select what you want to convert and paste special
multiply.


"Angi" wrote:

BJ,

Using this function, gave me a response of Zero "0" and I'm sure that isn't
correct. Should I use the CTRL ALT ENTER?

Ang

"bj" wrote:

Countif does not work well with multiple criteria.
the sumproduct function would work better try
=sumproduct(--(a1:a100=1),--(b1:b100=2),--(c1:c100=3))

"Angi" wrote:

I'm trying to create a countif function with three criteria, but for the life
of me I can't figure out the nested function. For example, I want to countif
column A=1, and column b=2 and column c=3. Can anyone help me with this?

Thank you so much!

for dates make the section be (C1-C100=datevalue(datetext))
You may have to play a bit with the format of the date to make it work the
way you want.

"Angi" wrote:

It worked on two columns, but the third column is a date and it isn't working.



"bj" wrote:

no sumproduct is an array function.
if you have the 1,2 3 combo in a row it should have worked (It did on mine)

are you sure that the 1,2 and 3 are numbers and not text?
you can check by seting up an equation such as
=(a1=1)
if it is a number 1 the answer will be true. if it is a text 1 the answer
will be false.
if there are text numbers one common way to change them to numbers is to put
a 1 someplace copy it and select what you want to convert and paste special
multiply.


"Angi" wrote:

BJ,

Using this function, gave me a response of Zero "0" and I'm sure that isn't
correct. Should I use the CTRL ALT ENTER?

Ang

"bj" wrote:

Countif does not work well with multiple criteria.
the sumproduct function would work better try
=sumproduct(--(a1:a100=1),--(b1:b100=2),--(c1:c100=3))

"Angi" wrote:

I'm trying to create a countif function with three criteria, but for the life
of me I can't figure out the nested function. For example, I want to countif
column A=1, and column b=2 and column c=3. Can anyone help me with this?

Thank you so much!

or =d1 where d1 has a date formatted the same

--
Don Guillett
SalesAid Software

"bj" wrote in message
...
for dates make the section be (C1-C100=datevalue(datetext))
You may have to play a bit with the format of the date to make it work the
way you want.

"Angi" wrote:

It worked on two columns, but the third column is a date and it isn't
working.



"bj" wrote:

no sumproduct is an array function.
if you have the 1,2 3 combo in a row it should have worked (It did on
mine)

are you sure that the 1,2 and 3 are numbers and not text?
you can check by seting up an equation such as
=(a1=1)
if it is a number 1 the answer will be true. if it is a text 1 the
answer
will be false.
if there are text numbers one common way to change them to numbers is
to put
a 1 someplace copy it and select what you want to convert and paste
special
multiply.


"Angi" wrote:

BJ,

Using this function, gave me a response of Zero "0" and I'm sure
that isn't
correct. Should I use the CTRL ALT ENTER?

Ang

"bj" wrote:

Countif does not work well with multiple criteria.
the sumproduct function would work better try
=sumproduct(--(a1:a100=1),--(b1:b100=2),--(c1:c100=3))

"Angi" wrote:

I'm trying to create a countif function with three criteria, but
for the life
of me I can't figure out the nested function. For example, I
want to countif
column A=1, and column b=2 and column c=3. Can anyone help me
with this?

Thank you so much!