Countifs Help

[rcreek09]

I thought I was grasping the countifs, but apparently not. Maybe I'm just missing something really obvious. Why is the following countifs not giving me a 1 on the first line? I couldn't get anything to copy/paste here so please excuse the poor example.


=COUNTIFS($H$7:$H$1584,H15>0,$D$7:$D$1584,D15=D16)


Col D has, on rows 15, 16:

2400016286

2400016286


Col H has:

24

0


Formula in ? column:

0

0


The formula dialog box for countifs shows me TRUE for both conditions for the first row, but gives me a 0 as the result. If I'm getting TRUE for both conditions, shouldn't that result in a count of 1?


Thanks very much,

Deb

 

[jeremymjp]

Well, the *criteria* is "TRUE", so it's looking for the value of "TRUE" in your ranges. Replace '2400016286' with the word "TRUE" and watch the result.


In words, what are you trying to get the formula to do? Just a guess, but I don't think your setup is right...


Let us know.

 

[Hui]

Rcreek09


There are a few issues here


The format for countifs is:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)


So the result of the criteria must be the same as the corresponding criteria_range

as Jermey said, your Criteria is evaluating to TRUE, but your criteria_range

is Numbers


Secondly the format of criteria1 H15>0 is incorrect

it should be H15&">0"


Finally I'm not sure if what your attempting is correct

You may want to try:

=COUNTIFS($H$7:$H$1584,H15&">0")+COUNTIFS($D$7:$D$1584,D15=D16)

 

[NARAYANK991]

Hi Deb ,


I think there is a misunderstanding in formulating the COUNTIFS function.


The criterion , specified in every even argument of COUNTIFS , is applied for every cell specified in the range argument ; thus if you specify only the following :


=COUNTIFS($H$7:$H$1584,">0")


then you will get a count of the number of positive values in the given range.


Thus , the criterion should be specified as a condition ; not as a TRUE or FALSE statement.


Suppose , instead , you have the value 24 in H15 , and you specify the criterion as any of the following :


"H15=24" or H15&"=24" or "H15"&"=24" or "H15="&24 or ...


you will get 0 as the result always.


This is because the criterion is already being evaluated as TRUE or FALSE. Hence , this criterion cannot be applied to any cell in the given range.


Narayan

 

[rcreek09]

Ok, I'm trying to process all the helpful comments. It sounds like countifs is not the best way to do what I'm doing. I have been able to get what I need using 'helper' columns, for this instance, but would like a better way in case I need to do it again.


Also, I want to get it straight in my head.


I have attempted to put a sample of the sheet with the formula in Google Docs:


https://docs.google.com/leaf?id=0ByeVouqBkD-KMWI5ZmE2NWMtOTE0My00ZDM4LWIxMjEtZTczNjM3ZGRjNjlk&hl=en_US


Hopefully this will better explain what/why I'm attempting, and hopefully the link and the permissions are ok.


Thanks to everyone for your help!

 

[NARAYANK991]

Hi Deb ,


Thanks for uploading the worksheet.


Have the following formula in cell F2 :


=COUNTIFS($B$2:$B$22,">0",$A$2:$A$22,E2)


When you enter the item number 2400016286 in cell E2 , F2 will display the corresponding count i.e. 5 ; when you change the item number in E2 to 2400016302 , the count will also change to 3. Otherwise , if you wish , you can copy the above formula down to cells F3 , F4 ,.... ; they will refer to corresponding item numbers in E3 , E4 ,....


There is really no need for the columns C and D.


Narayan

 

[Hui]

NARAYANK991,

You beat me by seconds, Well done

 

[rcreek09]

Hot Diggity!


That's EXACTLY what I wanted. As usual, I was making it harder than it really was.


Columns D, E, F I only added for this illustration.


Thanks again, SO much for your help!


I will make an effort to use countifs as often as possible to get better.

 

[NARAYANK991]

Hi Hui ,


Sorry ! Thanks.


Narayan