| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count of Number Values Greater Than, Less Than
How do you perform this count on a range of cells?
CountIf and SumProduct are both returning the wrong total, in more than one column. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count of Number Values Greater Than, Less Than
These should work...
=COUNTIF(A1:A10,"=7") =SUMPRODUCT(--(A1:A10=7)) If those don't work your values might be formatted as text, in which case try: =SUMPRODUCT(--(--A1:A10=7)) -- Regards, Dave "Anne" wrote: How do you perform this count on a range of cells? CountIf and SumProduct are both returning the wrong total, in more than one column. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count of Number Values Greater Than, Less Than
Thanks, but I need =0 AND <=36. It's the AND part that screws it all up.
=COUNTIF(D2:D9,"=0")-COUNTIF(D2:D9,"=<36") returns the wrong value. So does =SUMPRODUCT(--(D2:D9=0)--(D2:D9<=36)) Values are all formated as numbers. I can't figure out what it's counting. I just discovered that =SUM(IF((D2:D90)*(D2:D9<=36),1,0)) using ctrl+shift+enter works. I'm grateful that it does, but why does it perform a count when the function is to sum? "David Billigmeier" wrote: These should work... =COUNTIF(A1:A10,"=7") =SUMPRODUCT(--(A1:A10=7)) If those don't work your values might be formatted as text, in which case try: =SUMPRODUCT(--(--A1:A10=7)) -- Regards, Dave "Anne" wrote: How do you perform this count on a range of cells? CountIf and SumProduct are both returning the wrong total, in more than one column. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count of Number Values Greater Than, Less Than
Try this:
=SUMPRODUCT(--(D2:D90),--(D2:D9<36)) HTH, Paul "Anne" wrote in message ... Thanks, but I need =0 AND <=36. It's the AND part that screws it all up. =COUNTIF(D2:D9,"=0")-COUNTIF(D2:D9,"=<36") returns the wrong value. So does =SUMPRODUCT(--(D2:D9=0)--(D2:D9<=36)) Values are all formated as numbers. I can't figure out what it's counting. I just discovered that =SUM(IF((D2:D90)*(D2:D9<=36),1,0)) using ctrl+shift+enter works. I'm grateful that it does, but why does it perform a count when the function is to sum? "David Billigmeier" wrote: These should work... =COUNTIF(A1:A10,"=7") =SUMPRODUCT(--(A1:A10=7)) If those don't work your values might be formatted as text, in which case try: =SUMPRODUCT(--(--A1:A10=7)) -- Regards, Dave "Anne" wrote: How do you perform this count on a range of cells? CountIf and SumProduct are both returning the wrong total, in more than one column. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count of Number Values Greater Than, Less Than
maybe
=countif(d2:d9,"=0")-countif(d2:d9,"36") This way you are counting all values =0 and subtracting those values that are greater then the range you don't want to include. -- JNW "Anne" wrote: Thanks, but I need =0 AND <=36. It's the AND part that screws it all up. =COUNTIF(D2:D9,"=0")-COUNTIF(D2:D9,"=<36") returns the wrong value. So does =SUMPRODUCT(--(D2:D9=0)--(D2:D9<=36)) Values are all formated as numbers. I can't figure out what it's counting. I just discovered that =SUM(IF((D2:D90)*(D2:D9<=36),1,0)) using ctrl+shift+enter works. I'm grateful that it does, but why does it perform a count when the function is to sum? "David Billigmeier" wrote: These should work... =COUNTIF(A1:A10,"=7") =SUMPRODUCT(--(A1:A10=7)) If those don't work your values might be formatted as text, in which case try: =SUMPRODUCT(--(--A1:A10=7)) -- Regards, Dave "Anne" wrote: How do you perform this count on a range of cells? CountIf and SumProduct are both returning the wrong total, in more than one column. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count of Number Values Greater Than, Less Than
Oops! I forgot about the greater than or eaqual to:
=SUMPRODUCT(--(D2:D9=0),--(D2:D9<37)) "PCLIVE" wrote in message ... Try this: =SUMPRODUCT(--(D2:D90),--(D2:D9<36)) HTH, Paul "Anne" wrote in message ... Thanks, but I need =0 AND <=36. It's the AND part that screws it all up. =COUNTIF(D2:D9,"=0")-COUNTIF(D2:D9,"=<36") returns the wrong value. So does =SUMPRODUCT(--(D2:D9=0)--(D2:D9<=36)) Values are all formated as numbers. I can't figure out what it's counting. I just discovered that =SUM(IF((D2:D90)*(D2:D9<=36),1,0)) using ctrl+shift+enter works. I'm grateful that it does, but why does it perform a count when the function is to sum? "David Billigmeier" wrote: These should work... =COUNTIF(A1:A10,"=7") =SUMPRODUCT(--(A1:A10=7)) If those don't work your values might be formatted as text, in which case try: =SUMPRODUCT(--(--A1:A10=7)) -- Regards, Dave "Anne" wrote: How do you perform this count on a range of cells? CountIf and SumProduct are both returning the wrong total, in more than one column. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count of Number Values Greater Than, Less Than
To answer your question of why SUM(IF... counts:
Starting with the IF((D2:D90)*(D2:D9<=36), these two conditional checks return arrays of 1 and 0 (actually, they return TRUE and FALSE which are then coerced into 1 and 0 through the multiplication). When multiplied, these two arrays create one array of 1s and 0s. So now you have something like IF({1,1,0,1,0,0,0,1}...or whatever the results would actually be. Each of these elements are evaluated such as IF(1,1,0), IF(1,1,0), IF(0,1,0), etc. resulting in a further array of 1s and 0s. This final array is then summed, giving results that mimick a COUNT-type function. If you want to see this in action select the cell, then go to Tools,Formula Auditing, Evaluate Formula. "Anne" wrote in message ... Thanks, but I need =0 AND <=36. It's the AND part that screws it all up. =COUNTIF(D2:D9,"=0")-COUNTIF(D2:D9,"=<36") returns the wrong value. So does =SUMPRODUCT(--(D2:D9=0)--(D2:D9<=36)) Values are all formated as numbers. I can't figure out what it's counting. I just discovered that =SUM(IF((D2:D90)*(D2:D9<=36),1,0)) using ctrl+shift+enter works. I'm grateful that it does, but why does it perform a count when the function is to sum? "David Billigmeier" wrote: These should work... =COUNTIF(A1:A10,"=7") =SUMPRODUCT(--(A1:A10=7)) If those don't work your values might be formatted as text, in which case try: =SUMPRODUCT(--(--A1:A10=7)) -- Regards, Dave "Anne" wrote: How do you perform this count on a range of cells? CountIf and SumProduct are both returning the wrong total, in more than one column. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do i count the number of unique values in a given column? | Excel Discussion (Misc queries) | |||
| count number of values between plus signs in addition calc | Excel Discussion (Misc queries) | |||
| Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
| Count number of values in a cell | Excel Worksheet Functions | |||
Linear Mode
