Solved How to count Column values vs another Column

February 10, 2016 at 02:09:44
Specs: Windows 7
I am trying to count values in Column B that are greater than values in Column A & then return a "yes" or "no". Ideally, I would like the "yes" or "no" to be a response for the whole column. So if I had a columns A&B with 20 values each that had 19 rows showing B values less than A values but had 1 row showing otherwise e.g B3>A3, the response for Col B should be "yes". Otherwise if all Col B values are less than their counterparts in Col A, then I would like the response for Col B to be "no"

Thank you all


See More: How to count Column values vs another Column

Report •


✔ Best Answer
February 10, 2016 at 16:24:28
re: For some reason even if all my values in Col B are smaller than my Col A values, it's still throwing me a "Yes".

Not for me. With all values in Column B less than (or equal to ) its counterpart in Column A my formula returns No.

Perhaps you should post an example of your data so we can see what you are working with. Please click on the following line and read the instructions on how to post example data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
February 10, 2016 at 05:01:28
I belive that this formula will accomplish your goal:

=IF(SUMPRODUCT(--(B1:B20>A1:A20))>0,"Yes","No")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 10, 2016 at 06:19:50
Hi DerbyDad03,

For some reason even if all my values in Col B are smaller than my Col A values, it's still throwing me a "Yes". The formula should be comparing Col B vs Col A row by row, if all of my Col B values are less than their Col A counterparts then it should be showing "No". Where am I going wrong?

Thanks


Report •

#3
February 10, 2016 at 09:30:06
Not sure I understand your question.

If all values in A are less than B then Column is No
BUT
If any value in A is greater than B then Column is Yes

If that is what you want then the formula DerbyDad suggested works for me.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
February 10, 2016 at 16:24:28
✔ Best Answer
re: For some reason even if all my values in Col B are smaller than my Col A values, it's still throwing me a "Yes".

Not for me. With all values in Column B less than (or equal to ) its counterpart in Column A my formula returns No.

Perhaps you should post an example of your data so we can see what you are working with. Please click on the following line and read the instructions on how to post example data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question