Counting occurences in last 150 rows of 1000

[Legacy 178656]

Hi,
I have 4 columns with numbers in ranging from 1 to 99.
.. A B C D
1
2
...
1699


I add new entry every day.
( 1 entry = 4 new numbers in A to D)

What I'm trying to do on another sheet is to count the number of occurrences of each number (1 to 99) but only in the last (x) entries.

So if I'm on row 1700 I just want to count how many times each number occurs from row 1700-(x).

x will be the value placed in a cell which I can change eg 150.

I've been at this for hours and can't work it out. Any help would be appreciated before I go round the twist.

Thanks, Pete.

 

[Legacy 178656]

Oh, and the title is a bit misleading, it should be, "Counting occurences in last x rows of total rows" not 1000

 

[Aladin Akyurek]

Sheet1, A:D, is supposed to house data.

Sheet2

E2 houses the parameter value like 150 to indicate "x last records".

=COUNTIF(OFFSET(INDEX(Sheet1!A:A,MATCH(9.99E+307,Sheet1!A:A)),0,0,-E2,4),7)

This counts the occurrences of the number 7.

Adjust to suit.

 

[Legacy 178656]

OMG!

Genius, I salute you!
This works and so fast too.

I'll try and work out how this works.
Again thank you.

Pete

 

[Aladin Akyurek]

OMG!

Genius, I salute you!
This works and so fast too.

I'll try and work out how this works.
Again thank you.

Pete

You are welcome. Thanks for providing feedback.

Although what follows is a bit terse, the OFFSET bit constructs using the calculated last cell in A a range of E2 rows high and 4 columns wide. This range is then fed to the outer COUNTIF as the reference to look at.