Vvlookup and count
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 |
Vvlookup and count
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 |
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 |
Vvlookup and count
In that case make the SUMPRODUCT() formual:
=SUMPRODUCT((Sheet2!B2:B47="A")*(YEAR(Sheet2!A2:A4 7)=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 Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... 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=200 7)) 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 Replace @mailinator.com with @tiscali.co.uk "Freddy" wrote in message ... 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 |
Vvlookup and count
then perhaps
=SUMPRODUCT((A1:A27="Site 1")*(YEAR(B1:B27)=2004)) Mike "Freddy" wrote: 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 |
All times are GMT +1. The time now is 10:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com