VLookup to sum cell values

[Zakynthos]

I'm using a formula to sum values in cells corresponding to time slots in the
day such as 08:00, 08:15 etc and have produced a macro to extract these
timeslots prior to using the formula.

Some of the source data, however, has errors and occasionally an additional
time slot will appear e.g. 08:00 where in fact 08:15 should be.

In order to accurately produce an error-free summary of the values at each
time slot I realise that VLookup could do what I want, but I'm not sure
exactly how to write the formula.

For example, if the data on my sheet was as follows:

Monday 3/1/05 08:15 456 (cell ref: F105)
Monday 10/1/05 08:15 789 (cell ref: F469) etc etc

and I wanted to sum 456 and 789 and all other values for 08:15 for January '05

1. what VLookup formula would I use to achieve this?
2. How would I then copy tis new total to a summary table in another sheet
on this Workbook?

[Bob Phillips]

=SUMPRODUCT(--(A2:A20=--"2005-09-16 08:15"),B2:B20)

--
HTH

Bob Phillips

"Zakynthos" <[email protected]> wrote in message
news:[email protected]...
> I'm using a formula to sum values in cells corresponding to time slots in
the
> day such as 08:00, 08:15 etc and have produced a macro to extract these
> timeslots prior to using the formula.
>
> Some of the source data, however, has errors and occasionally an
additional
> time slot will appear e.g. 08:00 where in fact 08:15 should be.
>
> In order to accurately produce an error-free summary of the values at each
> time slot I realise that VLookup could do what I want, but I'm not sure
> exactly how to write the formula.
>
> For example, if the data on my sheet was as follows:
>
> Monday 3/1/05 08:15 456 (cell ref: F105)
> Monday 10/1/05 08:15 789 (cell ref: F469) etc etc
>
> and I wanted to sum 456 and 789 and all other values for 08:15 for January
'05
>
> 1. what VLookup formula would I use to achieve this?
> 2. How would I then copy tis new total to a summary table in another sheet
> on this Workbook?
>

[Zakynthos]

Many thanks!

Tony

"Bob Phillips" wrote:

> =SUMPRODUCT(--(A2:A20=--"2005-09-16 08:15"),B2:B20)
>
> --
> HTH
>
> Bob Phillips
>
> "Zakynthos" <[email protected]> wrote in message
> news:[email protected]...
> > I'm using a formula to sum values in cells corresponding to time slots in
> the
> > day such as 08:00, 08:15 etc and have produced a macro to extract these
> > timeslots prior to using the formula.
> >
> > Some of the source data, however, has errors and occasionally an
> additional
> > time slot will appear e.g. 08:00 where in fact 08:15 should be.
> >
> > In order to accurately produce an error-free summary of the values at each
> > time slot I realise that VLookup could do what I want, but I'm not sure
> > exactly how to write the formula.
> >
> > For example, if the data on my sheet was as follows:
> >
> > Monday 3/1/05 08:15 456 (cell ref: F105)
> > Monday 10/1/05 08:15 789 (cell ref: F469) etc etc
> >
> > and I wanted to sum 456 and 789 and all other values for 08:15 for January
> '05
> >
> > 1. what VLookup formula would I use to achieve this?
> > 2. How would I then copy tis new total to a summary table in another sheet
> > on this Workbook?
> >
>
>
>

[Zakynthos]

(I've tried this formula on my workbook as:

=SUMPRODUCT(--(A2:A20=--"2005-01-03 08:15"),B2:B20), and it produces a ZERO.

I input this formula in COLUMN G ROW 105 (this is the first row with data
for Monday in January)

This row contains in cells:

a105: Monday
b105: 03/01/2005
c105: 08:15
d105: 2
e105: 369
f105: a formula to convert d105*e105 and all other values in c/d at 08:15
in the month to a weighted average for that time in that month)

Why am I getting a '0' with the above formula?

How should I adapt this exactly to input the results of the above total
monthly weighted averages into a table on another sheet in this workbook, say
in E8 on the previous sheet?



"Bob Phillips" wrote:

> =SUMPRODUCT(--(A2:A20=--"2005-09-16 08:15"),B2:B20)
>
> --
> HTH
>
> Bob Phillips
>
> "Zakynthos" <[email protected]> wrote in message
> news:[email protected]...
> > I'm using a formula to sum values in cells corresponding to time slots in
> the
> > day such as 08:00, 08:15 etc and have produced a macro to extract these
> > timeslots prior to using the formula.
> >
> > Some of the source data, however, has errors and occasionally an
> additional
> > time slot will appear e.g. 08:00 where in fact 08:15 should be.
> >
> > In order to accurately produce an error-free summary of the values at each
> > time slot I realise that VLookup could do what I want, but I'm not sure
> > exactly how to write the formula.
> >
> > For example, if the data on my sheet was as follows:
> >
> > Monday 3/1/05 08:15 456 (cell ref: F105)
> > Monday 10/1/05 08:15 789 (cell ref: F469) etc etc
> >
> > and I wanted to sum 456 and 789 and all other values for 08:15 for January
> '05
> >
> > 1. what VLookup formula would I use to achieve this?
> > 2. How would I then copy tis new total to a summary table in another sheet
> > on this Workbook?
> >
>
>
>

[Bob Phillips]

Because it doesn't bear any relationship to your data, but to what you SAID
that your data looked like

Try

=SUMPRODUCT(--(B2:B105=--"2005-01-03"),--(C2:C105=--"08:15"),F2:F105)

you have to adapt to the data, don't expect an example to work exactly

--
HTH

Bob Phillips

"Zakynthos" <[email protected]> wrote in message
news:[email protected]...
> (I've tried this formula on my workbook as:
>
> =SUMPRODUCT(--(A2:A20=--"2005-01-03 08:15"),B2:B20), and it produces a
ZERO.
>
> I input this formula in COLUMN G ROW 105 (this is the first row with data
> for Monday in January)
>
> This row contains in cells:
>
> a105: Monday
> b105: 03/01/2005
> c105: 08:15
> d105: 2
> e105: 369
> f105: a formula to convert d105*e105 and all other values in c/d at 08:15
> in the month to a weighted average for that time in that month)
>
> Why am I getting a '0' with the above formula?
>
> How should I adapt this exactly to input the results of the above total
> monthly weighted averages into a table on another sheet in this workbook,
say
> in E8 on the previous sheet?
>
>
>
> "Bob Phillips" wrote:
>
> > =SUMPRODUCT(--(A2:A20=--"2005-09-16 08:15"),B2:B20)
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Zakynthos" <[email protected]> wrote in message
> > news:[email protected]...
> > > I'm using a formula to sum values in cells corresponding to time slots
in
> > the
> > > day such as 08:00, 08:15 etc and have produced a macro to extract
these
> > > timeslots prior to using the formula.
> > >
> > > Some of the source data, however, has errors and occasionally an
> > additional
> > > time slot will appear e.g. 08:00 where in fact 08:15 should be.
> > >
> > > In order to accurately produce an error-free summary of the values at
each
> > > time slot I realise that VLookup could do what I want, but I'm not
sure
> > > exactly how to write the formula.
> > >
> > > For example, if the data on my sheet was as follows:
> > >
> > > Monday 3/1/05 08:15 456 (cell ref: F105)
> > > Monday 10/1/05 08:15 789 (cell ref: F469) etc etc
> > >
> > > and I wanted to sum 456 and 789 and all other values for 08:15 for
January
> > '05
> > >
> > > 1. what VLookup formula would I use to achieve this?
> > > 2. How would I then copy tis new total to a summary table in another
sheet
> > > on this Workbook?
> > >
> >
> >
> >