Solved

Return count of values less than a value in another row

Posted on 2013-11-23
4
345 Views
Last Modified: 2013-11-23
I have two rows:

Row 1: 5 4 3 4 3 4 4 5 4
Row 2: 4 5 5 3 4 4 4 4 3

I need to count the number of occurrences that a value in Row 2 is less than its corresponding column in Row 1.  

In other words, is Row 2/Column A less than Row 1/Column A and so on.

So in this example, the total count would be 3 (columns A, D, I).
0
Comment
Question by:mcnuttlaw
4 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 500 total points
ID: 39671578
=SUMPRODUCT(--(A1:I1<A2:I2))

BTW the answer should be 4
A, D, H, I
0
 
LVL 81

Accepted Solution

by:
byundt earned 0 total points
ID: 39671583
ssaqibh has the right approach, but inadvertently counted row 1 being less than row 2. He meant to say:
=SUMPRODUCT(--(A2:I2<A1:I1))
0
 
LVL 2

Author Comment

by:mcnuttlaw
ID: 39671589
Awesome, thanks.

And I stand corrected!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question