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" wrote in message
...
(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" wrote in message
...
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?
|