| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNT BETWEEN A RANGE AND
HI
I WOULD LIKE TO COUNT VALUES BETWEEN A RANGE. THE FORMULA I HAVE TRIED TO USE IS: COUNTIF(D:D,"30000AND<75000") TO GET THE NUMBER OF VALUES BETWEEN 30 AND 75, HOWEVER, THIS DOES NOT WORK. CAN SOMEONE HELP? ALSO, ON TOP OF THIS I WOULD LIKE TO COUNT A STATEMENT IN ANOTHER COLUMN WHICH SAYS YES OR NO. SO THAT FROM THE VALUES BETWEEN 30 AND 75 I WOULD LIKE TO KNOW WHICH ONES ARE ALSO YES AND NO. ANYONE WHO CAN HELP ME MUCH APPRECIATED |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNT BETWEEN A RANGE AND
Either use
=COUNTIF(D:D,"30000")-COUNTIF(D:D,"75000") OR =SUMPRODUCT(--(D1:D100030000),--(D1:D1000<75000)) With another condition =SUMPRODUCT(--(C1:C1000="Y"),--(D1:D100030000),--(D1:D1000<75000)) =SUMPRODUCT(--(C1:C1000="N"),--(D1:D100030000),--(D1:D1000<75000)) -- If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: HI I WOULD LIKE TO COUNT VALUES BETWEEN A RANGE. THE FORMULA I HAVE TRIED TO USE IS: COUNTIF(D:D,"30000AND<75000") TO GET THE NUMBER OF VALUES BETWEEN 30 AND 75, HOWEVER, THIS DOES NOT WORK. CAN SOMEONE HELP? ALSO, ON TOP OF THIS I WOULD LIKE TO COUNT A STATEMENT IN ANOTHER COLUMN WHICH SAYS YES OR NO. SO THAT FROM THE VALUES BETWEEN 30 AND 75 I WOULD LIKE TO KNOW WHICH ONES ARE ALSO YES AND NO. ANYONE WHO CAN HELP ME MUCH APPRECIATED |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNT BETWEEN A RANGE AND
Hi Jacob
Thanks for this. The SUMPRODUCT formula doesn seem to be working as it stands. What do the 2 hyphens represent~? "Jacob Skaria" wrote: Either use =COUNTIF(D:D,"30000")-COUNTIF(D:D,"75000") OR =SUMPRODUCT(--(D1:D100030000),--(D1:D1000<75000)) With another condition =SUMPRODUCT(--(C1:C1000="Y"),--(D1:D100030000),--(D1:D1000<75000)) =SUMPRODUCT(--(C1:C1000="N"),--(D1:D100030000),--(D1:D1000<75000)) -- If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: HI I WOULD LIKE TO COUNT VALUES BETWEEN A RANGE. THE FORMULA I HAVE TRIED TO USE IS: COUNTIF(D:D,"30000AND<75000") TO GET THE NUMBER OF VALUES BETWEEN 30 AND 75, HOWEVER, THIS DOES NOT WORK. CAN SOMEONE HELP? ALSO, ON TOP OF THIS I WOULD LIKE TO COUNT A STATEMENT IN ANOTHER COLUMN WHICH SAYS YES OR NO. SO THAT FROM THE VALUES BETWEEN 30 AND 75 I WOULD LIKE TO KNOW WHICH ONES ARE ALSO YES AND NO. ANYONE WHO CAN HELP ME MUCH APPRECIATED |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNT BETWEEN A RANGE AND
You must be using 2003. SUMPRODUCT should work with a defined range...Try
with D1:D1000 =SUMPRODUCT(--(D1:D10030000),--(D1:D100<75000)) Refer this link for -- http://www.mcgimpsey.com/excel/formulae/doubleneg.html If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: Hi Jacob Thanks for this. The SUMPRODUCT formula doesn seem to be working as it stands. What do the 2 hyphens represent~? "Jacob Skaria" wrote: Either use =COUNTIF(D:D,"30000")-COUNTIF(D:D,"75000") OR =SUMPRODUCT(--(D1:D100030000),--(D1:D1000<75000)) With another condition =SUMPRODUCT(--(C1:C1000="Y"),--(D1:D100030000),--(D1:D1000<75000)) =SUMPRODUCT(--(C1:C1000="N"),--(D1:D100030000),--(D1:D1000<75000)) -- If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: HI I WOULD LIKE TO COUNT VALUES BETWEEN A RANGE. THE FORMULA I HAVE TRIED TO USE IS: COUNTIF(D:D,"30000AND<75000") TO GET THE NUMBER OF VALUES BETWEEN 30 AND 75, HOWEVER, THIS DOES NOT WORK. CAN SOMEONE HELP? ALSO, ON TOP OF THIS I WOULD LIKE TO COUNT A STATEMENT IN ANOTHER COLUMN WHICH SAYS YES OR NO. SO THAT FROM THE VALUES BETWEEN 30 AND 75 I WOULD LIKE TO KNOW WHICH ONES ARE ALSO YES AND NO. ANYONE WHO CAN HELP ME MUCH APPRECIATED |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNT BETWEEN A RANGE AND
Hi
The formula works now for values between 30k and 75k as I have put these values to record the correct amount of rows, however, the formula: =SUMPRODUCT(--(C1:C1000="N"),--(D1:D100030000),--(D1:D1000<75000)) does not work, I have written it as: =SUMPRODUCT(--(F1:F5000="Yes"),--(D1:D500030000),--(D1:D5000<75000)) and this records a value of 256 (which is wrong) it sould be 324. If I change it to "No" it records a value of 1,014 (again incorrect) it should 948. "Jacob Skaria" wrote: You must be using 2003. SUMPRODUCT should work with a defined range...Try with D1:D1000 =SUMPRODUCT(--(D1:D10030000),--(D1:D100<75000)) Refer this link for -- http://www.mcgimpsey.com/excel/formulae/doubleneg.html If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: Hi Jacob Thanks for this. The SUMPRODUCT formula doesn seem to be working as it stands. What do the 2 hyphens represent~? "Jacob Skaria" wrote: Either use =COUNTIF(D:D,"30000")-COUNTIF(D:D,"75000") OR =SUMPRODUCT(--(D1:D100030000),--(D1:D1000<75000)) With another condition =SUMPRODUCT(--(C1:C1000="Y"),--(D1:D100030000),--(D1:D1000<75000)) =SUMPRODUCT(--(C1:C1000="N"),--(D1:D100030000),--(D1:D1000<75000)) -- If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: HI I WOULD LIKE TO COUNT VALUES BETWEEN A RANGE. THE FORMULA I HAVE TRIED TO USE IS: COUNTIF(D:D,"30000AND<75000") TO GET THE NUMBER OF VALUES BETWEEN 30 AND 75, HOWEVER, THIS DOES NOT WORK. CAN SOMEONE HELP? ALSO, ON TOP OF THIS I WOULD LIKE TO COUNT A STATEMENT IN ANOTHER COLUMN WHICH SAYS YES OR NO. SO THAT FROM THE VALUES BETWEEN 30 AND 75 I WOULD LIKE TO KNOW WHICH ONES ARE ALSO YES AND NO. ANYONE WHO CAN HELP ME MUCH APPRECIATED |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNT BETWEEN A RANGE AND
MY APOLOGIES!!! IT DOES WORK!!
THANKS!! "Dave" wrote: Hi The formula works now for values between 30k and 75k as I have put these values to record the correct amount of rows, however, the formula: =SUMPRODUCT(--(C1:C1000="N"),--(D1:D100030000),--(D1:D1000<75000)) does not work, I have written it as: =SUMPRODUCT(--(F1:F5000="Yes"),--(D1:D500030000),--(D1:D5000<75000)) and this records a value of 256 (which is wrong) it sould be 324. If I change it to "No" it records a value of 1,014 (again incorrect) it should 948. "Jacob Skaria" wrote: You must be using 2003. SUMPRODUCT should work with a defined range...Try with D1:D1000 =SUMPRODUCT(--(D1:D10030000),--(D1:D100<75000)) Refer this link for -- http://www.mcgimpsey.com/excel/formulae/doubleneg.html If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: Hi Jacob Thanks for this. The SUMPRODUCT formula doesn seem to be working as it stands. What do the 2 hyphens represent~? "Jacob Skaria" wrote: Either use =COUNTIF(D:D,"30000")-COUNTIF(D:D,"75000") OR =SUMPRODUCT(--(D1:D100030000),--(D1:D1000<75000)) With another condition =SUMPRODUCT(--(C1:C1000="Y"),--(D1:D100030000),--(D1:D1000<75000)) =SUMPRODUCT(--(C1:C1000="N"),--(D1:D100030000),--(D1:D1000<75000)) -- If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: HI I WOULD LIKE TO COUNT VALUES BETWEEN A RANGE. THE FORMULA I HAVE TRIED TO USE IS: COUNTIF(D:D,"30000AND<75000") TO GET THE NUMBER OF VALUES BETWEEN 30 AND 75, HOWEVER, THIS DOES NOT WORK. CAN SOMEONE HELP? ALSO, ON TOP OF THIS I WOULD LIKE TO COUNT A STATEMENT IN ANOTHER COLUMN WHICH SAYS YES OR NO. SO THAT FROM THE VALUES BETWEEN 30 AND 75 I WOULD LIKE TO KNOW WHICH ONES ARE ALSO YES AND NO. ANYONE WHO CAN HELP ME MUCH APPRECIATED |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNT BETWEEN A RANGE AND
Check your data.
Either your values aren't what you think they are or maybe you have hidden rows??? Maybe you could add another column with a formula to help you find the mismatches: Insert a new column G and put this in G1: =(f1="yes")&"--"&(d130000)&"--"&(d1<75000) And drag down. Now find one of the rows that you think should be counted and you should see TRUE--TRUE--TRUE. Your job will be to find the rows that aren't all true and determine why. I'm betting either typing mistakes (extra characters in column F) or text that looks like numbers in column D. Dave wrote: Hi The formula works now for values between 30k and 75k as I have put these values to record the correct amount of rows, however, the formula: =SUMPRODUCT(--(C1:C1000="N"),--(D1:D100030000),--(D1:D1000<75000)) does not work, I have written it as: =SUMPRODUCT(--(F1:F5000="Yes"),--(D1:D500030000),--(D1:D5000<75000)) and this records a value of 256 (which is wrong) it sould be 324. If I change it to "No" it records a value of 1,014 (again incorrect) it should 948. "Jacob Skaria" wrote: You must be using 2003. SUMPRODUCT should work with a defined range...Try with D1:D1000 =SUMPRODUCT(--(D1:D10030000),--(D1:D100<75000)) Refer this link for -- http://www.mcgimpsey.com/excel/formulae/doubleneg.html If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: Hi Jacob Thanks for this. The SUMPRODUCT formula doesn seem to be working as it stands. What do the 2 hyphens represent~? "Jacob Skaria" wrote: Either use =COUNTIF(D:D,"30000")-COUNTIF(D:D,"75000") OR =SUMPRODUCT(--(D1:D100030000),--(D1:D1000<75000)) With another condition =SUMPRODUCT(--(C1:C1000="Y"),--(D1:D100030000),--(D1:D1000<75000)) =SUMPRODUCT(--(C1:C1000="N"),--(D1:D100030000),--(D1:D1000<75000)) -- If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: HI I WOULD LIKE TO COUNT VALUES BETWEEN A RANGE. THE FORMULA I HAVE TRIED TO USE IS: COUNTIF(D:D,"30000AND<75000") TO GET THE NUMBER OF VALUES BETWEEN 30 AND 75, HOWEVER, THIS DOES NOT WORK. CAN SOMEONE HELP? ALSO, ON TOP OF THIS I WOULD LIKE TO COUNT A STATEMENT IN ANOTHER COLUMN WHICH SAYS YES OR NO. SO THAT FROM THE VALUES BETWEEN 30 AND 75 I WOULD LIKE TO KNOW WHICH ONES ARE ALSO YES AND NO. ANYONE WHO CAN HELP ME MUCH APPRECIATED -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNT BETWEEN A RANGE AND
Great...
If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: MY APOLOGIES!!! IT DOES WORK!! THANKS!! "Dave" wrote: Hi The formula works now for values between 30k and 75k as I have put these values to record the correct amount of rows, however, the formula: =SUMPRODUCT(--(C1:C1000="N"),--(D1:D100030000),--(D1:D1000<75000)) does not work, I have written it as: =SUMPRODUCT(--(F1:F5000="Yes"),--(D1:D500030000),--(D1:D5000<75000)) and this records a value of 256 (which is wrong) it sould be 324. If I change it to "No" it records a value of 1,014 (again incorrect) it should 948. "Jacob Skaria" wrote: You must be using 2003. SUMPRODUCT should work with a defined range...Try with D1:D1000 =SUMPRODUCT(--(D1:D10030000),--(D1:D100<75000)) Refer this link for -- http://www.mcgimpsey.com/excel/formulae/doubleneg.html If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: Hi Jacob Thanks for this. The SUMPRODUCT formula doesn seem to be working as it stands. What do the 2 hyphens represent~? "Jacob Skaria" wrote: Either use =COUNTIF(D:D,"30000")-COUNTIF(D:D,"75000") OR =SUMPRODUCT(--(D1:D100030000),--(D1:D1000<75000)) With another condition =SUMPRODUCT(--(C1:C1000="Y"),--(D1:D100030000),--(D1:D1000<75000)) =SUMPRODUCT(--(C1:C1000="N"),--(D1:D100030000),--(D1:D1000<75000)) -- If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: HI I WOULD LIKE TO COUNT VALUES BETWEEN A RANGE. THE FORMULA I HAVE TRIED TO USE IS: COUNTIF(D:D,"30000AND<75000") TO GET THE NUMBER OF VALUES BETWEEN 30 AND 75, HOWEVER, THIS DOES NOT WORK. CAN SOMEONE HELP? ALSO, ON TOP OF THIS I WOULD LIKE TO COUNT A STATEMENT IN ANOTHER COLUMN WHICH SAYS YES OR NO. SO THAT FROM THE VALUES BETWEEN 30 AND 75 I WOULD LIKE TO KNOW WHICH ONES ARE ALSO YES AND NO. ANYONE WHO CAN HELP ME MUCH APPRECIATED |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNT BETWEEN A RANGE AND
Everything is working fine. Thanks!
"Dave Peterson" wrote: Check your data. Either your values aren't what you think they are or maybe you have hidden rows??? Maybe you could add another column with a formula to help you find the mismatches: Insert a new column G and put this in G1: =(f1="yes")&"--"&(d130000)&"--"&(d1<75000) And drag down. Now find one of the rows that you think should be counted and you should see TRUE--TRUE--TRUE. Your job will be to find the rows that aren't all true and determine why. I'm betting either typing mistakes (extra characters in column F) or text that looks like numbers in column D. Dave wrote: Hi The formula works now for values between 30k and 75k as I have put these values to record the correct amount of rows, however, the formula: =SUMPRODUCT(--(C1:C1000="N"),--(D1:D100030000),--(D1:D1000<75000)) does not work, I have written it as: =SUMPRODUCT(--(F1:F5000="Yes"),--(D1:D500030000),--(D1:D5000<75000)) and this records a value of 256 (which is wrong) it sould be 324. If I change it to "No" it records a value of 1,014 (again incorrect) it should 948. "Jacob Skaria" wrote: You must be using 2003. SUMPRODUCT should work with a defined range...Try with D1:D1000 =SUMPRODUCT(--(D1:D10030000),--(D1:D100<75000)) Refer this link for -- http://www.mcgimpsey.com/excel/formulae/doubleneg.html If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: Hi Jacob Thanks for this. The SUMPRODUCT formula doesn seem to be working as it stands. What do the 2 hyphens represent~? "Jacob Skaria" wrote: Either use =COUNTIF(D:D,"30000")-COUNTIF(D:D,"75000") OR =SUMPRODUCT(--(D1:D100030000),--(D1:D1000<75000)) With another condition =SUMPRODUCT(--(C1:C1000="Y"),--(D1:D100030000),--(D1:D1000<75000)) =SUMPRODUCT(--(C1:C1000="N"),--(D1:D100030000),--(D1:D1000<75000)) -- If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: HI I WOULD LIKE TO COUNT VALUES BETWEEN A RANGE. THE FORMULA I HAVE TRIED TO USE IS: COUNTIF(D:D,"30000AND<75000") TO GET THE NUMBER OF VALUES BETWEEN 30 AND 75, HOWEVER, THIS DOES NOT WORK. CAN SOMEONE HELP? ALSO, ON TOP OF THIS I WOULD LIKE TO COUNT A STATEMENT IN ANOTHER COLUMN WHICH SAYS YES OR NO. SO THAT FROM THE VALUES BETWEEN 30 AND 75 I WOULD LIKE TO KNOW WHICH ONES ARE ALSO YES AND NO. ANYONE WHO CAN HELP ME MUCH APPRECIATED -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count within a Range Q | Excel Worksheet Functions | |||
| Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions | |||
| Using a count in a range name | Excel Discussion (Misc queries) | |||
| How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
| Count cells in one range based on parameters in another range | Excel Worksheet Functions | |||
Linear Mode
