Vvlookup and count
The dates are actually done by day - should have mentioned that:
Example:
Site1 01/01/2003
Site2 15/3/2004
Site3 24/6/2005
Site4 19/9/2007
Site2 21/6/2005
Site1 14/4/2004
In the summarised table on the other sheet, my vlookup for the 2003 column
is as follows:
=vlookup(C3, Sheet1!B1:B105, 2, 0)
B1:B105 = I sorted the table by oldest date first so this is the range for
all the 2003 values only.
If the site is recognised, I only get a date. I even tried putting COUNT
infront of the vlookup function by only get '1' or '0'.
"Mike H" wrote:
Freddy,
I may have misunderstood but this looks for Site 1 in column A and counts
how many times 2004 appears in the corresponding cell in column B
=SUMPRODUCT((A1:A27="Site 1")*(B1:B27=2004))
Mike
"Freddy" wrote:
I have a spreadsheet with a 450 rows and 2 columns
The first column is a list of sites and the second is a list dates
(2003-2007).
The second sheet is a summarised table of the sites and I have been trying
to produce a vlookup function that will also count how many times the dates
appear for those sites .
Does anyone know how to get the vlookup to count all the dates rather than
just display the first date it comes to.
I am trying to do this for 4 columns - 2003-2007
Thanks very much,
Freddy
|