Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif comparing two columns
Please help.
I have two colums of data I need to compare, roughly 100 rows each. I need to count a given row if the value in column b is greater than the number in column a. There are a few alpha letters in each row. I have scoured and tested everything I have seen posted regarding countif, sumif, sumproduct, etc. and I can't seem to get it to work. Any assistance would be greatly appreciated. Thanks in advance, John |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif comparing two columns
Why don't you post some sample data and explain what you are expecting..so
that some one can help you OR point you in the right direction. "Johndb" wrote: Please help. I have two colums of data I need to compare, roughly 100 rows each. I need to count a given row if the value in column b is greater than the number in column a. There are a few alpha letters in each row. I have scoured and tested everything I have seen posted regarding countif, sumif, sumproduct, etc. and I can't seem to get it to work. Any assistance would be greatly appreciated. Thanks in advance, John |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif comparing two columns
Do you mean the below...where you have data in ColA and ColB..the below
formula will count the rows in which both columns are numerics and colB is greater than ColA. Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =SUM(IF(ISNUMBER(A1:A100),IF(ISNUMBER(B1:B100),IF( B1:B100A1:A100,1,0)))) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Why don't you post some sample data and explain what you are expecting..so that some one can help you OR point you in the right direction. "Johndb" wrote: Please help. I have two colums of data I need to compare, roughly 100 rows each. I need to count a given row if the value in column b is greater than the number in column a. There are a few alpha letters in each row. I have scoured and tested everything I have seen posted regarding countif, sumif, sumproduct, etc. and I can't seem to get it to work. Any assistance would be greatly appreciated. Thanks in advance, John |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif comparing two columns
Jacob,
This was perfect, I am an intermediate user and I wasn't anywhere near this line of thought. Thanks a heap. Warmest Regards, John "Jacob Skaria" wrote: Do you mean the below...where you have data in ColA and ColB..the below formula will count the rows in which both columns are numerics and colB is greater than ColA. Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =SUM(IF(ISNUMBER(A1:A100),IF(ISNUMBER(B1:B100),IF( B1:B100A1:A100,1,0)))) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Why don't you post some sample data and explain what you are expecting..so that some one can help you OR point you in the right direction. "Johndb" wrote: Please help. I have two colums of data I need to compare, roughly 100 rows each. I need to count a given row if the value in column b is greater than the number in column a. There are a few alpha letters in each row. I have scoured and tested everything I have seen posted regarding countif, sumif, sumproduct, etc. and I can't seem to get it to work. Any assistance would be greatly appreciated. Thanks in advance, John |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif comparing two columns
Hi,
You could try this =SUMPRODUCT(ISNUMBER(D3:D9)*ISNUMBER(E3:E9)*(D3:D9 E3:E9)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Johndb" wrote in message ... Please help. I have two colums of data I need to compare, roughly 100 rows each. I need to count a given row if the value in column b is greater than the number in column a. There are a few alpha letters in each row. I have scoured and tested everything I have seen posted regarding countif, sumif, sumproduct, etc. and I can't seem to get it to work. Any assistance would be greatly appreciated. Thanks in advance, John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comparing 2 columns | Excel Discussion (Misc queries) | |||
Comparing to columns | Excel Discussion (Misc queries) | |||
COUNTIF/SUMIF comparing two rows of data | Excel Worksheet Functions | |||
Comparing two columns of information with 2 new columns of informa | Excel Discussion (Misc queries) | |||
comparing columns | Excel Discussion (Misc queries) |