I have a table of about 700 items (no duplicates) that is taken from a big table where data is being added on every day. All of these items in the first mentioned table occur more then once in the big table, so I have added a count next to the second table to see how many times one item is 'mentioned' in the big file. Alongside, I successfully made a chart, displaying the top 100 most mentioned items.
However, I want that same list to be within a date range. Right now I am fetching all data which results in the number 1 item returning a value of 14000 times being 'mentioned'. Adding a date range would make it more visible for me to see how many times that item is being 'mentioned' between the specific dates.
The table with the 700 unique items is called Table4 and has the columns Node & Count. My formula to find the top hits:
=LARGE(Table4[Count], D2)
(where D2 is rank number '1', copied down to row 100)
So to conclude, I know how many times item number 1 is being 'mentioned' in the big list, but how do I find out how many times item number 1 is being mentioned from September 1st to September 30th (or any date range for that matter)?
Thanks!
If you are using a COUNTIF() formula today to determine the count in your really big table (Table1) of each unique item in Table4. You could switch to a COUNTIFS() formula instead:
Supposing your "Node" is in column A of your really big table (Table1) and "Date" is in column B of the really big table, then change your Countif('Table1'!A:A,A1) to:
=Countifs('Table1'!A:A,A1,'Table1'!B:B, ">=09/01/2014", 'Table1'!B:B, "<=09/30/2014")
Now you have your original criteria 'Table1'!A:A=A1 and two new criteria to test for the date range you are looking for.