Can anyone suggest a formula for this?
Basically, I'm trying to find out how many times does a number on the data 1
column exceed the numbers on the data 2 column.

Data 1 Data 2 Value
4 3
5 18
10 1
4 2

The result should say for the 'value' column 3, since 4 3,1,2 but less than
18
similiarly for 5, the result should be 3 and so on.

Thanks, I tried the countif method, but does not help.

Hi,

Assuming you are starting in row 1 put this in c1 and drag down to solve for
each value in Column A

=COUNT(IF(A1$B$1:$B$10,$B$1:$B$10,FALSE))

It's an array formula so coommit with Ctrl+Shift+Enter NOT just enter.

Mike

"Rocky4460" wrote:

Can anyone suggest a formula for this?
Basically, I'm trying to find out how many times does a number on the data 1
column exceed the numbers on the data 2 column.

Data 1 Data 2 Value
4 3
5 18
10 1
4 2

The result should say for the 'value' column 3, since 4 3,1,2 but less than
18
similiarly for 5, the result should be 3 and so on.

Thanks, I tried the countif method, but does not help.

This formula does what you want:

=SUMPRODUCT(--(A2B$2:B$5))

where the heading Data1 is in A1, Data2 in B1 and Value in C1 - put
the formula in C2, adjust the ranges to suit your real data, then copy
down.

Hope this helps.

Pete

On Jul 31, 7:31*pm, Rocky4460
wrote:
Can anyone suggest a formula for this?
Basically, I'm trying to find out how many times does a number on the data 1
column exceed the numbers on the data 2 column.

Data 1 * * Data 2 * * * * * * Value
4 * * * * * * * *3
5 * * * * * * * *18
10 * * * * * * *1
4 * * * * * * * 2

The result should say for the 'value' column 3, since 4 3,1,2 but less than
18
similiarly for 5, the result should be 3 and so on.

Thanks, I tried the countif method, but does not help.

Thanks, Mate. It worked.

It also worked with =COUNTIF(B$1:B$4,"<=",&A1)

I'd like to learn more of SumProduct function.

Can you explain what SumProduct actually does and what the two hyphenes are?

Appreciate it.

"Pete_UK" wrote:

This formula does what you want:

=SUMPRODUCT(--(A2B$2:B$5))

where the heading Data1 is in A1, Data2 in B1 and Value in C1 - put
the formula in C2, adjust the ranges to suit your real data, then copy
down.

Hope this helps.

Pete

On Jul 31, 7:31 pm, Rocky4460
wrote:
Can anyone suggest a formula for this?
Basically, I'm trying to find out how many times does a number on the data 1
column exceed the numbers on the data 2 column.

Data 1 Data 2 Value
4 3
5 18
10 1
4 2

The result should say for the 'value' column 3, since 4 3,1,2 but less than
18
similiarly for 5, the result should be 3 and so on.

Thanks, I tried the countif method, but does not help.

Glad it worked for you.

The definitive guide to SUMPRODUCT is on Bob Phillips' site:

http://www.xldynamic.com/source/xld.html

However, the site seems to be down at the moment, so maybe try again
later.

The double unary minus -- converts the TRUE and FALSE into 1 and 0 for
each cell in the range, and these then get added together.

Hope this helps.

Pete

On Jul 31, 9:56*pm, Rocky4460
wrote:
Thanks, Mate. It worked.

It also worked with =COUNTIF(B$1:B$4,"<=",&A1)

I'd like to learn more of SumProduct function.

Can you explain what SumProduct actually does and what the two hyphenes are?

Appreciate it.



"Pete_UK" wrote:
This formula does what you want:

=SUMPRODUCT(--(A2B$2:B$5))

where the heading Data1 is in A1, Data2 in B1 and Value in C1 - put
the formula in C2, adjust the ranges to suit your real data, then copy
down.

Hope this helps.

Pete

On Jul 31, 7:31 pm, Rocky4460
wrote:
Can anyone suggest a formula for this?
Basically, I'm trying to find out how many times does a number on the data 1
column exceed the numbers on the data 2 column.

Data 1 * * Data 2 * * * * * * Value
4 * * * * * * * *3
5 * * * * * * * *18
10 * * * * * * *1
4 * * * * * * * 2

The result should say for the 'value' column 3, since 4 3,1,2 but less than
18
similiarly for 5, the result should be 3 and so on.

Thanks, I tried the countif method, but does not help.- Hide quoted text -

- Show quoted text -

Bob's site seems to be back again now. Click on this link to take you
directly to the section about SUMPRODUCT:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Hope this helps.

Pete

On Jul 31, 10:55*pm, Pete_UK wrote:
Glad it worked for you.

The definitive guide to SUMPRODUCT is on Bob Phillips' site:

http://www.xldynamic.com/source/xld.html

However, the site seems to be down at the moment, so maybe try again
later.

The double unary minus -- converts the TRUE and FALSE into 1 and 0 for
each cell in the range, and these then get added together.

Hope this helps.

Pete

On Jul 31, 9:56*pm, Rocky4460
wrote:



Thanks, Mate. It worked.

It also worked with =COUNTIF(B$1:B$4,"<=",&A1)

I'd like to learn more of SumProduct function.

Can you explain what SumProduct actually does and what the two hyphenes are?

Appreciate it.

"Pete_UK" wrote:
This formula does what you want:

=SUMPRODUCT(--(A2B$2:B$5))

where the heading Data1 is in A1, Data2 in B1 and Value in C1 - put
the formula in C2, adjust the ranges to suit your real data, then copy
down.

Hope this helps.

Pete

On Jul 31, 7:31 pm, Rocky4460
wrote:
Can anyone suggest a formula for this?
Basically, I'm trying to find out how many times does a number on the data 1
column exceed the numbers on the data 2 column.

Data 1 * * Data 2 * * * * * * Value
4 * * * * * * * *3
5 * * * * * * * *18
10 * * * * * * *1
4 * * * * * * * 2

The result should say for the 'value' column 3, since 4 3,1,2 but less than
18
similiarly for 5, the result should be 3 and so on.

Thanks, I tried the countif method, but does not help.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -