VLookup to sum cell values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
(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?
 
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
 
Back
Top