write a query to retrieve data
 

I have three sheets in my workbook: Request sheet with request number
and date,Fault sheet with request number and faults count,and in the
third sheet I have month start date and end date. I want to retrieve
data such that : sum of all the faults of those requests which fall in
the month given in third sheet

Request Sheet
Request ID ActualEnd Date
1 1/22/2006
2 2/27/2006
3 1/22/2006
4 1/22/2006
5 1/22/2006

Fault Sheet
Request ID Total

1 6.75
2 3.75
3 7.50

third sheet
Jan-06 1/1/2006 1/31/2006 to get value

write a query to retrieve data
 

Hi,
There may be a better way of organizing your data, basically combining
the first two sheets.
However, keeping to the format you already have, this is what I would
suggest. This assumes that row 2 in Request and Fault is for ID #1, and
row 3 is ID #2 etc, and there are 100 rows of data. Also that the third
sheet has the month in A1, the start of the month in B1 (Start Date),
and the end of the month in C1 (End Date).
Type the following in D1 and (importantly) press *Ctrl Shift Enter *to
accept the formula; this makes it an 'array' formula and you will see
{} around the formula; it won't work if you just press enter.
=SUM(IF(Request!B2:B100=B1,IF(Request!B2:B100<=C1 ,Fault!B2:B100,0),0))

What the formula basically says is if the dates in Request are = Start
Date AND dates in Request are <= End Date, then sum those same rows in
Fault sheet.

You may already be doing this, but to get Excel to calculate the Start
Date automatically where the month is shown in A1 of the third sheet,
put this in B1:
=A1-DAY(A1)+1
This formula calcuates the first day of the month for any date. You
could use a simpler formula in your example, but this formula might be
useful in the future.
For Excel to calculate the end date in cell C1, simply use =B2-1 where
it is taking one day off the first day of the next month. Or look at
the =EODATE formula.

Hope that helps. Let me know if it works.
Clive


--
Clivey_UK
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=521668