Help with a formula please

[Robert Gillard]

I have 2 work sheets - sheet1 lists daily items i.e. entry date, no of
entries, amount of entries etc (one day per row), sheet2 is a summary of the
daily totals. I have been asked to produce a summary of the daily totals on
a weekly basis. So on sheet1 we will have 5 rows, but on the summary sheet
the 5 daily totals for (say) no. of entries will all be added together to
give just one grand total for the week.

I have added a column on sheet1 to give me the week no., but now I want to
put a formula on sheet2 that in simple English will say if on sheet1 the
weekno = 26 then sum the no. of entries. In addition I want to be able to
drag the formula down each week so the weekno. will automatically increase
in line with the current week.

Could anybody help me with this please.

Bob

[Barb Reinhardt]

I'd probably use a pivot table to do the first part.

"Robert Gillard" <bob@mystical.demon.co.uk> wrote in message
news:s%Jse.12545$Vo6.6857@fe3.news.blueyonder.co.uk...
>I have 2 work sheets - sheet1 lists daily items i.e. entry date, no of
>entries, amount of entries etc (one day per row), sheet2 is a summary of
>the daily totals. I have been asked to produce a summary of the daily
>totals on a weekly basis. So on sheet1 we will have 5 rows, but on the
>summary sheet the 5 daily totals for (say) no. of entries will all be added
>together to give just one grand total for the week.
>
> I have added a column on sheet1 to give me the week no., but now I want to
> put a formula on sheet2 that in simple English will say if on sheet1 the
> weekno = 26 then sum the no. of entries. In addition I want to be able to
> drag the formula down each week so the weekno. will automatically increase
> in line with the current week.
>
> Could anybody help me with this please.
>
> Bob
>

[Bill Kuunders]

for the total number of entries

on sheet 2 in cell A2 enter the week #
in B2 you can enter the following formula
=SUM(IF(Sheet1!$A$2:$A$300=A2,Sheet1!$C$2:$C$300,0))
entered as an array function
hold <cntrl><shift> and push <enter>

this will change the formula adding the {} brackets at start and end
{=SUM(IF(Sheet1!$A$2:$A$300=A2,Sheet1!$C$2:$C$300,0))}

the area's need to be same size
I guessed the 300...........

you could use names for your area's and the formula looks a bit tidier.
=SUM(IF(weeks=A2,tot_entries,0))

second part of your question
just extend the series down........
i.e under A2 extend the week number.........1,2,3,4etc 52

extend the formula in B2 using the right hand bottom corner of the cell
mouse pointer will change to "+"
left click and drag down.

ps
any time you edit the formula you need to use the <cntrl><shift> and push
<enter>
routine

--
Greetings from New Zealand
Bill K
"Barb Reinhardt" <reply@tonewsgroup.com> wrote in message
news:OvYZOtAdFHA.2760@tk2msftngp13.phx.gbl...
> I'd probably use a pivot table to do the first part.
>
> "Robert Gillard" <bob@mystical.demon.co.uk> wrote in message
> news:s%Jse.12545$Vo6.6857@fe3.news.blueyonder.co.uk...
>>I have 2 work sheets - sheet1 lists daily items i.e. entry date, no of
>>entries, amount of entries etc (one day per row), sheet2 is a summary of
>>the daily totals. I have been asked to produce a summary of the daily
>>totals on a weekly basis. So on sheet1 we will have 5 rows, but on the
>>summary sheet the 5 daily totals for (say) no. of entries will all be
>>added together to give just one grand total for the week.
>>
>> I have added a column on sheet1 to give me the week no., but now I want
>> to put a formula on sheet2 that in simple English will say if on sheet1
>> the weekno = 26 then sum the no. of entries. In addition I want to be
>> able to drag the formula down each week so the weekno. will automatically
>> increase in line with the current week.
>>
>> Could anybody help me with this please.
>>
>> Bob
>>
>
>