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

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
 
M

Max

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top