| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting number of times data matches
I have two columns of numerical data in range a1:b40. The cells hold values
from 1 to 9. I want to count the number of times 'column a' data is less than or equal to 'column b' data. How do I do this? -- Thank you, Colin. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of times data matches
Colin
If you want to do it with a single array formula, try: =SUM(--(A1:A40<B1:B40)) entered as an array (shift-ctrl-enter) Good luck. Ken Norfolk, Va On Sep 11, 12:48 pm, Colin wrote: I have two columns of numerical data in range a1:b40. The cells hold values from 1 to 9. I want to count the number of times 'column a' data is less than or equal to 'column b' data. How do I do this? -- Thank you, Colin. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of times data matches
If you have numerical entries in all cells you can get around using
=SUMPRODUCT(--(A1:A40<=B1:B40)) but it will count blank cells and text =SUMPRODUCT(--(A1:A40<=B1:B40),--(ISNUMBER(A1:A40))) will take care of blank cells =SUMPRODUCT(--(A1:A40<=B1:B40),--(ISNUMBER(A1:A40)),--(ISNUMBER(B1:B40))) will take care of blank cells and text -- Regards, Peo Sjoblom "Colin" wrote in message ... I have two columns of numerical data in range a1:b40. The cells hold values from 1 to 9. I want to count the number of times 'column a' data is less than or equal to 'column b' data. How do I do this? -- Thank you, Colin. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of times data matches
Hello,
Thank you all for your suggestions. Without realising it, I did need to avoid counting cells with text in it. The arrays and the Count formula both worked fine. -- Thank you, Colin. "Peo Sjoblom" wrote: If you have numerical entries in all cells you can get around using =SUMPRODUCT(--(A1:A40<=B1:B40)) but it will count blank cells and text =SUMPRODUCT(--(A1:A40<=B1:B40),--(ISNUMBER(A1:A40))) will take care of blank cells =SUMPRODUCT(--(A1:A40<=B1:B40),--(ISNUMBER(A1:A40)),--(ISNUMBER(B1:B40))) will take care of blank cells and text -- Regards, Peo Sjoblom "Colin" wrote in message ... I have two columns of numerical data in range a1:b40. The cells hold values from 1 to 9. I want to count the number of times 'column a' data is less than or equal to 'column b' data. How do I do this? -- Thank you, Colin. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of times data matches
Hi,
Try this array:-= =COUNT(IF(A1:A40<=B1:B20,A1:A40,FALSE)) enter with Ctrl+Shift+enter Mike "Colin" wrote: I have two columns of numerical data in range a1:b40. The cells hold values from 1 to 9. I want to count the number of times 'column a' data is less than or equal to 'column b' data. How do I do this? -- Thank you, Colin. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of times data matches
Hi,
Try this array:-= =COUNT(IF(A1:A40<=B1:B40,A1:A40,FALSE)) enter with Ctrl+Shift+enter Mike "Colin" wrote: I have two columns of numerical data in range a1:b40. The cells hold values from 1 to 9. I want to count the number of times 'column a' data is less than or equal to 'column b' data. How do I do this? -- Thank you, Colin. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting number of times certain Text Values appear | Excel Discussion (Misc queries) | |||
| Create Chart by Counting Number of Times Data is Used | Excel Discussion (Misc queries) | |||
| counting the number of times something is bought during a month | Excel Worksheet Functions | |||
| how do I count the number of times text in column A matches text i | Excel Worksheet Functions | |||
| Counting the number of times someone called in sick | Excel Discussion (Misc queries) | |||
Hybrid Mode
