Combine FREQUENCY and SUM of Associated Values
 

I have two columns of data:
Col A1:A200 are Distances (not all cells have values in them)
Col B1:B200 are Values associated with each Distance, but some Values are
positive and some are Negative
I have been successful in calculating the Frequency of the various values
within each Distance Bin (col C1:C10)
I need two more columns contiguous to the Distance Bin showing:
Sum of positive values for each frequency in the Bin
Sum of Negative Values for each frequency in the Bin

I tried:
{=SUM(IF(FREQUENCY(MATCH(A1:A200,C1:C10,1),MATCH(B 1:B200:C1:C10,1))0,""))}
for the positive Column, but get #NA

I would greatly appreciate any help and assistance.

Hi!

Sumproduct is much easier:

For positive values:

Enter this formula in D2:

=SUMPRODUCT(--($A$1:$A$200$C1),--($A$1:$A$200<=$C2),--($B$1:$B$2000),$B$1:$B$200)

Copy across to E2 then edit the formula in C2 and change: --($B$1:$B$2000)
to --($B$1:$B$200<0)

Then select both D2 and E2 and copy down to row 10.

Biff

"MichaelC" wrote in message
...
I have two columns of data:
Col A1:A200 are Distances (not all cells have values in them)
Col B1:B200 are Values associated with each Distance, but some Values are
positive and some are Negative
I have been successful in calculating the Frequency of the various values
within each Distance Bin (col C1:C10)
I need two more columns contiguous to the Distance Bin showing:
Sum of positive values for each frequency in the Bin
Sum of Negative Values for each frequency in the Bin

I tried:
{=SUM(IF(FREQUENCY(MATCH(A1:A200,C1:C10,1),MATCH(B 1:B200:C1:C10,1))0,""))}
for the positive Column, but get #NA

I would greatly appreciate any help and assistance.

THANK YOU Biff! (OK to shout appreciation, right?)

"Biff" wrote:

Hi!

Sumproduct is much easier:

For positive values:

Enter this formula in D2:

=SUMPRODUCT(--($A$1:$A$200$C1),--($A$1:$A$200<=$C2),--($B$1:$B$2000),$B$1:$B$200)

Copy across to E2 then edit the formula in C2 and change: --($B$1:$B$2000)
to --($B$1:$B$200<0)

Then select both D2 and E2 and copy down to row 10.

Biff

"MichaelC" wrote in message
...
I have two columns of data:
Col A1:A200 are Distances (not all cells have values in them)
Col B1:B200 are Values associated with each Distance, but some Values are
positive and some are Negative
I have been successful in calculating the Frequency of the various values
within each Distance Bin (col C1:C10)
I need two more columns contiguous to the Distance Bin showing:
Sum of positive values for each frequency in the Bin
Sum of Negative Values for each frequency in the Bin

I tried:
{=SUM(IF(FREQUENCY(MATCH(A1:A200,C1:C10,1),MATCH(B 1:B200:C1:C10,1))0,""))}
for the positive Column, but get #NA

I would greatly appreciate any help and assistance.

Feedback is always welcome!

There was a typo but it sounds like you figured it out:

Copy across to E2 then edit the formula in C2 and change.........

Should read:

Copy across to E2 then edit the formula in E2 and change.........

Biff

"MichaelC" wrote in message
...
THANK YOU Biff! (OK to shout appreciation, right?)

"Biff" wrote:

Hi!

Sumproduct is much easier:

For positive values:

Enter this formula in D2:

=SUMPRODUCT(--($A$1:$A$200$C1),--($A$1:$A$200<=$C2),--($B$1:$B$2000),$B$1:$B$200)

Copy across to E2 then edit the formula in C2 and
change: --($B$1:$B$2000)
to --($B$1:$B$200<0)

Then select both D2 and E2 and copy down to row 10.

Biff

"MichaelC" wrote in message
...
I have two columns of data:
Col A1:A200 are Distances (not all cells have values in them)
Col B1:B200 are Values associated with each Distance, but some Values
are
positive and some are Negative
I have been successful in calculating the Frequency of the various
values
within each Distance Bin (col C1:C10)
I need two more columns contiguous to the Distance Bin showing:
Sum of positive values for each frequency in the Bin
Sum of Negative Values for each frequency in the Bin

I tried:
{=SUM(IF(FREQUENCY(MATCH(A1:A200,C1:C10,1),MATCH(B 1:B200:C1:C10,1))0,""))}
for the positive Column, but get #NA

I would greatly appreciate any help and assistance.