Vvlookup and count

[Freddy]

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

 

[Mike H]

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

 

[Sandy Mann]

A Pivot table will do what you want very quickly. With the columns labelled
Dates and Sites drag Dates into the ROW of the Pivot table and Dates in the
COLUMN and also the Data area but double click on it if it says "Sum of
Dates" and select Count.

Alternatively, use a SUMPRODUCT() formual like:

=SUMPRODUCT((Sheet2!B1:B47="A")*(Sheet2!A1:A47=2007))

But this may be slow with a large amount of data.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Freddy]

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

 

[Sandy Mann]

In that case make the SUMPRODUCT() formual:

=SUMPRODUCT((Sheet2!B2:B47="A")*(YEAR(Sheet2!A2:A47)=2007))

Make sure that the ranges do not include the labels, (which my previous
formula did.

For the Pivot table option use an helper column with the formula:

=Year(A2) and copy down.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Mike H]

then perhaps

=SUMPRODUCT((A1:A27="Site 1")*(YEAR(B1:B27)=2004))

Mike