# Count last 10 items of data

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
###### Who is Participating?

Excel VBA DeveloperCommented:
I got a very good solution to the "last N data" problem from krishnakrkc in my repackaged question and am applying that here.  These steps supercede what I posted above.

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"&A1&":C"&A3)>(INDIRECT("D"&A1&":D"&A3))))
3) In cell D1, insert
=SUMPRODUCT(--(INDIRECT("C"&A1&":C"&A3)<(INDIRECT("D"&A1&":D"&A3))))

Modified file attached.
EE-CountLast10W-SHEET3b.xlsx
0

Commented:
Refer to Sheet2 of the attached. In L2 this formula is used to find the lower end of your range:

=(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"&\$L2&":C"&\$L3)>INDIRECT("D"&\$L2&":D"&\$L3)))

If this works for you, just hide column L!

Flyster
EE-CountLast10.xlsx
0

Commented:
This copy has all cells in row 2 formatted and column L hidden.
EE-CountLast10.xlsx
0

Author Commented:
Flyster,

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
0

Excel VBA DeveloperCommented:
I tried to create an array function that would calculate the row where the last ten games recorded starts, but was not able to combine OFFSET/COUNT functions that generate dynamic ranges (I'll probably post my own question on that).

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:
=(COUNT(OFFSET(\$C\$5,ROW()-5,0,20,1))=10)*ROW()
2) In cell A1, insert this function
=MAX(F1:F300)
This determines the top row for the last 10 games
3) In cell C1, insert
=SUMPRODUCT(--(INDIRECT("C"&A1&":C"&A3)>(INDIRECT("D"&A1&":D"&A3))))
4) In cell D1, insert
=SUMPRODUCT(--(INDIRECT("C"&A1&":C"&A3)<(INDIRECT("D"&A1&":D"&A3))))

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
0

Excel VBA DeveloperCommented:
Pedro,

Regards,
-Glenn
0

Author Commented:
Flyster, Glenn:

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.
0

Excel VBA DeveloperCommented:
Thanks for recognizing our contributions.

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
0

Author Commented:
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.
0

Excel VBA DeveloperCommented:
Sure.  Here is an example file that I created when I first started working with your data.

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
0

Author Commented:
Glenn,

Looks like it will mean using different formulas to get to the results, am I correct?
0

Excel VBA DeveloperCommented:
It would, but in most cases the formulas would be simpler.  I noted that some of the formulas in that example file aren't correct or complete (my doing), but I think they could be updated in a short timeframe.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.