Be seen. Boost your questionâ€™s priority for more expert views and faster solutions

Hi everyone,

I am looking for a way to count the last 10 items that are greather than and less than in a particular set of cells.

I am using the following formula for the less than and greather count.

=SUMPRODUCT(--(C13:C22>D13:D22)) = formula in cell C2

=SUMPRODUCT(--(C13:C22<D13:D22)) = formula in cell D2

This gets the job done with some issues.

First, I have to manually change the cell ranges to match after data is added.

Second, any blanks in between may be omitted so that I do not get the full 10 count range I want.

Does anyone know of a way to do this without manually changing the cell ranges each time data is added.

P.S. I tried using INDIRECT("C" & B2) to use the value in cell B2 as the last cell number but I get an error when inserted in place of the static cell range used in the formula.

EE-CountLast10.xlsx

I am looking for a way to count the last 10 items that are greather than and less than in a particular set of cells.

I am using the following formula for the less than and greather count.

=SUMPRODUCT(--(C13:C22>D13

=SUMPRODUCT(--(C13:C22<D13

This gets the job done with some issues.

First, I have to manually change the cell ranges to match after data is added.

Second, any blanks in between may be omitted so that I do not get the full 10 count range I want.

Does anyone know of a way to do this without manually changing the cell ranges each time data is added.

P.S. I tried using INDIRECT("C" & B2) to use the value in cell B2 as the last cell number but I get an error when inserted in place of the static cell range used in the formula.

EE-CountLast10.xlsx

=(COUNTA(C4:C1000))-5

It counts all non-blank cells from C4 to C1000. 5 is subtracted to account for the fact your data starts in row 4 and to be 1 less then max. This formula is used in L3:

=MATCH(9.9E+307,C:C,1)

It is just one of the many out there to find the last non-blank cell in a column. These two are used for the variable's for your formula's in row 2. C2 uses your original formula with indirect used to handle the variables:

=SUMPRODUCT(--(INDIRECT("C

If this works for you, just hide column L!

Flyster

EE-CountLast10.xlsx

This copy has all cells in row 2 formatted and column L hidden.

EE-CountLast10.xlsx

EE-CountLast10.xlsx

Don't know what I am doing wrong.

I am trying to adapt the formulas you gave in the worksheet. (See Attached file sheet 3).

And I am not getting the desired results.

First I changed the formulas in 'L' to 'A' since I have other data in 'L'. Then formula in C1 gives me 37 which is higher than the total for last 10 win/losses.

I hope you can help me make this right.

EE-CountLast10W-SHEET3.xlsx

But in the meantime, I was able to resolve the issue by adding a helper column (in F) that calculates that starting row needed to then calculate the last 10 games. The result is then incorporated into two SUMPRODUCT functions for that subrange.

1) In column F, cell F5, insert this function and copy down:

2) In cell A1, insert this function

This determines the top row for the last 10 games

3) In cell C1, insert

4) In cell D1, insert

The sum of C1 and D1 should always total to 10. I added the most-recent game results for Baltimore and the tally appears correct.

Modified file attached.

-Glenn

EE-CountLast10W-SHEET3b.xlsx

I am going to accept both of your solutions. It appears that it is too complicated for me. I am trying to apply it to other teams and am not able to do so without erros.

That said, I am using another solution to get around this problem. Both are getting egual points here for all your hard work.

Thank you,

P.S. It is not that your solution does not work it is just that I just do not know enough to apply it the way I need to.

I will say again - as I noted in my first experience with your work - that you should try to get all your data into a single data table as it will make retrieving and manipulating the data much, much easier. :-)

-Glenn

I am assuming you mean that I need to eliminate the lines between tables. I am not too familiar with what you speak of. How would I be able to differentiate between the different player stats? Would the formulas currently used still work?

Perhaps if you send me an attachement with the tables as you say they work best I would understand better.

This was in in your question "need code to pull data from one table and insert into another". I proposed that you combine all the pitcher records into one Excel Table so that you could have a singular source for query and also have the advantage of using PivotTables, if desired.

I believe because you are copying data from the MLB.com website, you find it cumbersome to combine all the data in this manner. However, I believe the reporting capability you gain will outweigh the data collection cost.

Regards,

-Glenn

EE-DataCalculations.xlsx

All Courses

From novice to tech pro — start learning today.

1) In cell A1, insert this array function ([Ctrl]+[Shift]+[Enter]):

=ROW(INDEX($C$5:$C$229,LARGE(IF($C$5:$C$229<>"",ROW($C$5:$C$229)),10)-4)) This determines the top row for the last 10 games

2) In cell C1, insert

=SUMPRODUCT(--(INDIRECT("C

3) In cell D1, insert

=SUMPRODUCT(--(INDIRECT("C

Modified file attached.

EE-CountLast10W-SHEET3b.xlsx