Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Angi
 
Posts: n/a
Default 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!

  #3   Report Post  
bj
 
Posts: n/a
Default

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!

  #4   Report Post  
Angi
 
Posts: n/a
Default

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!

  #5   Report Post  
bj
 
Posts: n/a
Default

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!



  #6   Report Post  
Angi
 
Posts: n/a
Default

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!

  #7   Report Post  
bj
 
Posts: n/a
Default

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!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
analysing data from alternate columns using the countif function Juniper Excel Discussion (Misc queries) 3 April 28th 05 04:12 PM
clock Wildman Excel Worksheet Functions 2 April 26th 05 10:31 AM
How do I use COUNTIF in a SUBTOTAL function to differentiate the . Lettie Excel Worksheet Functions 6 March 22nd 05 09:47 AM
countif function etan Excel Worksheet Functions 5 February 7th 05 01:55 AM
Nested IF Function, Date Comparing, and NetworkDays Function carl Excel Worksheet Functions 2 December 29th 04 10:57 PM


All times are GMT +1. The time now is 01:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"