Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif ?
I want a summary count of two criteria. (count if type = a and class =1)
In one column type is stored and in another column Classification is stored. I want the know how many type a, classification 1 exist I was trying to use countif, but can't seem to figure out how to do it for two criteria. can you use AND?? =COUNTIF(Sheet1!E:E,"IC") |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif ?
MT wrote:
I want a summary count of two criteria. (count if type = a and class =1) In one column type is stored and in another column Classification is stored. I want the know how many type a, classification 1 exist I was trying to use countif, but can't seem to figure out how to do it for two criteria. can you use AND?? =COUNTIF(Sheet1!E:E,"IC") You say that your criteria are a and 1 yet your COUNTIF contains IC??? Are your types and classes in separate columns? If so where? What actually are your criteria values? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif ?
Fluke, but I just happened to look into it this morning!
What you actually want is multiple-condition formula. If you seach Excel Help under "Sum", there is a great explanation of how to Sum using multiple conditions. I do not know why the multiple-condition formula option does not show when you seach "Count", but it does not. Basically, the key difference is that you have to use an array formula (another thing I learned this morning). The most important thing to remember with an array formula is to use brace brackets {} around the entire formula. You do this by either manually entering them, or by holding CTRL-SHIFT-ENTER after you finish typing the formula. The formula you use should look like this: {=COUNT(IF(($B$4:$B$8=$A24)*($E$4:$E$8=B$20),$C$4: $C$8))} Where A24 is the first condition you want met; B4:B8 is the column in which you want this condition to be found; B20 is the second condition you want met; E4: E8 is the column you want this condition to be found; C4:C8 is the column you want counted. Some items of note: I used reference cells, but if you want the condition to be a word, you can just type in "Apples" instead of A24 or B20. Also, I have found the exact column to be counted (C4:C8) to be somewhat irrelevant so long as it is any column in your array of data. Good luck! "MT" wrote: I want a summary count of two criteria. (count if type = a and class =1) In one column type is stored and in another column Classification is stored. I want the know how many type a, classification 1 exist I was trying to use countif, but can't seem to figure out how to do it for two criteria. can you use AND?? =COUNTIF(Sheet1!E:E,"IC") |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif ?
Try (changes ranges to suit):
=SUMPRODUCT(--(A1:A5="a"),--(B1:B5=1)) "jcc" wrote: Fluke, but I just happened to look into it this morning! What you actually want is multiple-condition formula. If you seach Excel Help under "Sum", there is a great explanation of how to Sum using multiple conditions. I do not know why the multiple-condition formula option does not show when you seach "Count", but it does not. Basically, the key difference is that you have to use an array formula (another thing I learned this morning). The most important thing to remember with an array formula is to use brace brackets {} around the entire formula. You do this by either manually entering them, or by holding CTRL-SHIFT-ENTER after you finish typing the formula. The formula you use should look like this: {=COUNT(IF(($B$4:$B$8=$A24)*($E$4:$E$8=B$20),$C$4: $C$8))} Where A24 is the first condition you want met; B4:B8 is the column in which you want this condition to be found; B20 is the second condition you want met; E4: E8 is the column you want this condition to be found; C4:C8 is the column you want counted. Some items of note: I used reference cells, but if you want the condition to be a word, you can just type in "Apples" instead of A24 or B20. Also, I have found the exact column to be counted (C4:C8) to be somewhat irrelevant so long as it is any column in your array of data. Good luck! "MT" wrote: I want a summary count of two criteria. (count if type = a and class =1) In one column type is stored and in another column Classification is stored. I want the know how many type a, classification 1 exist I was trying to use countif, but can't seem to figure out how to do it for two criteria. can you use AND?? =COUNTIF(Sheet1!E:E,"IC") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif Formula /Sort Bug??? | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
Countif - Countif | Excel Worksheet Functions | |||
countif, again | Excel Worksheet Functions |