Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Return count of values less than a value in another row

Posted on 2013-11-23
Medium Priority
361 Views
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
Question by:mcnuttlaw
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 43

Assisted Solution

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

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

LVL 81

Accepted Solution

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

ID: 39671589
Awesome, thanks.

And I stand corrected!
0

## Featured Post

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month8 days, 20 hours left to enroll