Sumif With Arrays Difficulty

[mcongdon]

Hey Guys,
I have a worksheet with 60,000 rows and 3 columns that I have to sort into ranges and then take averages of. Column A is # of shares, Column B is $ per share, and Column C is a calculation based off of these numbers.

I have a table that is divided into ranges like so:

29.99 19.99 9.99 4.99
99
74
49
24
9

Where the Y axis is # of shares and X axis is $/share.

I want to take the average of the calculation in the first sheet (Column C) only for the rows that match a range, so that the table gets filled with the data. For example, it would take the average of all those cells in column C where the share amount is 9 and under and the $/share is 4.99 and under and put it in the table above in the bottom right corner.

I have tried using sumif to find the sum for each range, then i would use the countif function in an identical table, then divide the two tables using ctrl+shift+enter. My formula that didn't work was:

=SUMIF(Data!$C$2:$C$60026,AND(Data!$A$2:$A$60026<10,Data!$B$2:$B$60026<5))

Any help would be awesome, if there is no way, then I have to sort it all by hand. :-(

 

[SkipVought]

Hi,

Your requirement is not clear. You need to post a more complete sample including values you want to sum and the expected result.

Here's a guess, though...
[tt]
=SUMPRODUCT((Data!$C$2:$C$60026)*(Data!$A$2:$A$60026<10)*(Data!$B$2:$B$60026<5))
[/tt]
Where does the DATA in row 1 enter into the computation?


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mcongdon]

For Example the data sheet would look like:

A B C
Shares Price $/Share
4 2.41 .05
3 2.15 .01
2 3.26 .03
1 4.21 .04
11 7.50 .15
17 6.29 .14
22 8.00 .17
20 9.23 .16

The Table would be filled in like so:

E F G H
29.99 19.99 9.99 4.99
99
74
49
24 .155
9 .0325


Where the .155 is the average of the rows column C where Column A is less than or equal to 24, but greater than 9 and Column B is less than or equal to 4.99, but greater than 0. The .0325 is the average of the rows in column C where Column A is less than or equal to 9, but greater than 0 and Column B is less than or equal to 4.99, but greater than 0.

The clincher is that the numbers are all out of order- I can't just find the end of each range. They have to stay in the current order as well. I can't just re-sort it.
Thanks!

 

[SkipVought]

did you try the formula I posted?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

You also need to provide the logic for the values in column D and row 1 with respect to the source table.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mcongdon]

I tried your formula, and it worked for the lowest ranges, but once I had to add an and() formula into it for the other ranges, it always returned 0.

This worked:
=SUMPRODUCT((Data!$C$2:$C$62026)*(Data!$A$2:$A$62026<=9)*(Data!$B$2:$B$62026<=5))

This didn't:
=SUMPRODUCT((Data!$C$2:$C$62026)*(AND(Data!$A$2:$A$62026<=24,Data!$A$2:$A$62026>9))*(AND(Data!$B$2:$B$62026<=9.99,Data!$B$2:$B$62026>4.99)))

 

[AnotherHiggins]

To get averages, you can divide one SumProduct formula by another.

Example:

[tab][COLOR=blue white]=SUMPRODUCT((Data!$A$2:$A$60026<=$A6) * (Data!$B$2:$B$60026<=E$1) * (Data!$C$2:$C$60026)) [red]/[/red] SUMPRODUCT((Data!$A$2:$A$60026<=$A6) * (Data!$B$2:$B$60026<=E$1))[/color]

The numerator (same as what Skip provided except with cell references) sums column C where columns A & B meet the criteria.

The denominator (same thing as the numerator, but without column C thrown in) counts the instances where columns A & B meet the criteria.

In order to calculate other cells, you'll need to add more criteria to the SumProducts, something like (in cell D5):

[tab][COLOR=blue white]=SUMPRODUCT((Data!$A$2:$A$60026<=$A5) * (Data!$A$2:$A$60026>$A6) * (Data!$B$2:$B$60026<=D$1) * (Data!$B$2:$B$60026>E$1) * (Data!$C$2:$C$60026)) [red]/[/red] SUMPRODUCT((Data!$A$2:$A$60026<=$A5) * (Data!$A$2:$A$60026>$A6) * (Data!$B$2:$B$60026<=D$1) * (Data!$B$2:$B$60026>E$1))[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

By the way - this might be a good time to employee Named Ranges. They will make it much easier to decipher what long formulas like this do later on.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mcongdon]

D E F G H
1 29.99 19.99 9.99 4.99
2 99
3 74
4 49
5 24
6 9

Column D is the number of shares. D6 is referring to any trade with 9 shares or less. D5 is anything <=24 shares but greater than 9.

H is the Price per Share. H1 is referring to any trade with a price of 4.99 or less. G1 is referring to any trade with price <=9.99 but greater than 4.99

Later I will use an Index and match functions with a -1 match type to pull values off this table if you get what I'm trying to do.

 

[SkipVought]

The ANDS are "built in"
[tt]
=SUMPRODUCT((Data!$C$2:$C$62026)*(Data!$A$2:$A$62026<=24)*(Data!$A$2:$A$62026>9)*(Data!$B$2:$B$62026<=9.99)*(Data!$B$2:$B$62026>4.99))
[/tt]
You ought to be using REFERENCES rather than hard values. ONE FORMULA that you can copy to ALL the cells in the calculation area.

Using Named Ranges...
[tt]
=SUMPRODUCT((Share)*(Shares<=$D2)*(Shares>$D3)*(Price<=E$1)*(Price>F$1))
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mcongdon]

Perfect again John! Thanks Skip for the sumproduct idea! This saved me weeks of sorting by hand...

 

[mcongdon]

I also forgot completely about named ranges- that makes it much much easier- thanks guys!

 

[AnotherHiggins]

By the way, the second formula in my post will work for all of the cells in your table, provided that you add a zero to the right of row 1 and at the bottom of column 1.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.