Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vvlookup and count
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
How do I throw in an ISERROR function on a complicated VVLOOKUP? | Excel Discussion (Misc queries) |