If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Counting
I have a list of scores 1.1, 3.2, 1.2, 2.4, 1.1, 3, etc... . I need to know
how many are between 1 and 2. Is this a countif? I can't seem to figure out how to use two conditions. |
#2
|
|||
|
|||
Counting
Try this:
=COUNTIF(A1:A10,"=1")-COUNTIF(A1:A10,"2") Or, use cells to hold the range: B1 = 1 C1 = 2 =COUNTIF(A1:A10,"="&B1)-COUNTIF(A1:A10,""&C1) Biff "AdamMCW" wrote in message ... I have a list of scores 1.1, 3.2, 1.2, 2.4, 1.1, 3, etc... . I need to know how many are between 1 and 2. Is this a countif? I can't seem to figure out how to use two conditions. |
#3
|
|||
|
|||
Counting
When you say *between* 1 and 2, do you mean *greater* then 1 and *less* then
2: =SUMPRODUCT((A1:A501)*(A1:A502)) OR, do you mean *greater then or equal* to 1 and *less then or equal* to 2: =SUMPRODUCT((A1:A50=1)*(A1:A50=2)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "AdamMCW" wrote in message ... I have a list of scores 1.1, 3.2, 1.2, 2.4, 1.1, 3, etc... . I need to know how many are between 1 and 2. Is this a countif? I can't seem to figure out how to use two conditions. |
#4
|
|||
|
|||
Counting
Thanks to you both.
I see how Biff's works with subtraction. Although yours looks more direct Ragdyer I'm not clear why it works. I'm assuming the "*" is not intended to multiply anything and is instead redifined when used with the Sumproduct function. Is this correct? "AdamMCW" wrote: I have a list of scores 1.1, 3.2, 1.2, 2.4, 1.1, 3, etc... . I need to know how many are between 1 and 2. Is this a countif? I can't seem to figure out how to use two conditions. |
#5
|
|||
|
|||
Counting
Hi Adam
Sumproduct is being used to calculate the outcome of 2 arrays. There are a series of tests which will return True or False, which upon multiplication will be coerced to 1 or 0 =SUMPRODUCT((A1:A50=1)*(A1:A50=2)) (A1:A50=1) will return either True or False (A1:A50=2) will return either True or False Multiplying them together will only give a result of 1, where both cases are True (1 * 1), and therefore both conditions are met. Sumproduct then adds all these results to give your answer. For more information on Sumproduct (and alternative ways to coerce True and False to 1 and 0), take a look at Bob Phillips excellent treatise at http://xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Roger Govier "AdamMCW" wrote in message ... Thanks to you both. I see how Biff's works with subtraction. Although yours looks more direct Ragdyer I'm not clear why it works. I'm assuming the "*" is not intended to multiply anything and is instead redifined when used with the Sumproduct function. Is this correct? "AdamMCW" wrote: I have a list of scores 1.1, 3.2, 1.2, 2.4, 1.1, 3, etc... . I need to know how many are between 1 and 2. Is this a countif? I can't seem to figure out how to use two conditions. |
Thread Tools | |
Display Modes | |
|
|