how do I specify a date range = date(2010,4,1 thru 30)

  • Thread starter Thread starter carper1975
  • Start date Start date
C

carper1975

I am trying to use a header in a formula. In A1 I have the month and year
(April 2010). I want to bring in a calculation result from a second page
(Daily Log) So I want to know how many guest in the month of April visited.
But it has to look up April from the year list.

I guess this is what I am trying to accomplish Lookup (A1) April (1-30) 2010
from April Stats sheet on Daily log sheet (A column) add guests logged in (D
Column) and result to April Stats B5

I record numbers daily and want to have the totals from one page brought to
another page, but need the data to be found for the month
 
Assuming the source data as described is in the sheet named: Log
real dates in col A, real numbers in col D

In the stats sheet,
just enter any real date for "Apr 2010" in A1, eg: 1-Apr-2010
then in B5, you could use this:
=SUMPRODUCT(--TEXT(Log!A2:A20,"mmmyy")=TEXT(A1,"mmmyy"),Log!D2:D20)
to return the sum of numbers in Log's col D with Apr 2010 dates in Log's col
A. Adapt the ranges to suit. I favour this route when it comes to computing
figs for a certain month/yr as you don't have to specify the actual start to
end dates (it can be "difficult" recalling the last day of month: 30?, 31?
ugh). Success? hit the YES below.
 
Back
Top