Should be

=SUMPRODUCT(--(A1:A1613=--"Saturday"),--(C1:C1613=--"08:15"),F1:F1613)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Zakynthos" <[email protected]> wrote in message
news:[email protected]...
> Yes, I tried it but it returned a #VALUE! error.
>
> I used this:
>
> =SUMPRODUCT(--(A1:A1613=--"Saturday"),(C1:C1613=--"08:15"),F1:F1613)
>
> Is the error related to the format of "08:15", or the fact that F1:F1613
the
> cells containing the values are formula themselves? or have I made a
mistake
> in the formula, it assumes that:
>
> Days of the week are in column and timeslots in column C
>
> If the SUMPRODUCT won't work, can I use the CONDITIONAL SUM WIZARD to set
> the 2 criteria, as I know it can do this, but I'm not quite clear how to
use
> it. The SUMPRODUCT looks simpler and I would prefer to get that to
work!!!
>
> "Bob Phillips" wrote:
>
> > Did you try the SUMPRODUCT solution offered earlier? If so, what was the
> > problem?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Zakynthos" <[email protected]> wrote in message
> > news:[email protected]...
> > > D105, E105 contain numerical values and these represeent the data used
to
> > > calculate the weighted average in F105 etc.
> > >
> > > However, on reviiewing the problem and reading further on SUMIF and
> > > SUMPRODUCT, I don't think (???) I could use either of these as there
are 2
> > > criteria to be considered in deciding which values are to be added.
> > >
> > > To restate the problem as clearly as I can:
> > >
> > > I want to add all the values corresponding to particular timeslots for
> > each
> > > day over a given month and also to check if, for some reason, a
criteria
> > is
> > > 'wrong', e.g. the wrong timeslot it showing in the right cell (say
08:00
> > is
> > > showing where 08:15 should show) that only values corresponding with
08:15
> > > will be summed.
> > >
> > > I want to perform this calculation with all values from 08:15 to 21:00
for
> > > all days of the week from Monday to Sunday for a month.
> > >
> > > Example: (Jan 2005) relevant info in worksheet shows in:
> > >
> > > A1: Saturday
> > > C1: 08:15
> > > F1 (Value to be summed, currently a formula, as in original post, for
> > > weighted average for all values at 8:15's on Mondays in January)
> > >
> > >
> > > I've tried this, but it's not quite right, is it?
> > >
> > > =IF(C105=0.04375,SUM(H105:H1613)
> > >
> > > (I've converted "08:15" from time format to a number and have retyped
the
> > > value in f105 as a number into h105) and this gives me an answer for
all
> > > 8:15's in the range f105 to f1613
> > >
> > > The problem arises when I try to include an additnal IF statement for
the
> > > 2nd criteria 'Monday'
> > >
> > > If I ADD in after the above:
> > >
> > > =(IF(A105="Monday",SUM(h105:h1613), it returns a 'TRUE'
> > >
> > > This reads (in full)
> > >
> > > =IF(C105=0.04375,SUM(H105:H1613)=(IF(A105="Monday",SUM(h105:h1613)
> > >
> > > So, 2 questions:
> > >
> > > How do I combine the 2 IF statements to get a result? (i.e. the sum of
the
> > > values)?
> > >
> > > Is there a function or Visual Basic code I could use to convert the
> > weighted
> > > average formula in f1:f1613 without the need to retype them all?
> > >
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > What are in the cells D105, E105 for example, results wise?
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Zakynthos" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I would like to use this function to sum particular values but I
> > realise
> > > > that
> > > > > the function will only work with numerical values.
> > > > >
> > > > > The values I wish to sum are weighted averages based on figures
which
> > are
> > > > > not in a range, and the formula shows the distribution of the
data:
> > > > >
> > > > >
> > > >
> >
=($D105*$E105)+($D469*$E469)+($D833*$E833)+($D1197*$E1197)+($D1561*$E1561)/(
> > > > $D105+$D469+$D833+$D1197+$D1561)
> > > > >
> > > > > I know that if I simply retype the values as numbers the SUMIF
will
> > work
> > > > > fine, but there is a vast amount of data involved and it will be
very
> > time
> > > > > consuming and tedious I'm hoping there is a way of converting the
> > formula
> > > > to
> > > > > a numerical equivalent in the adjacent row. I have tried =(g1) etc
but
> > > > this
> > > > > doesn't work with SUMIF either.
> > > > >
> > > > > Can it be done?
> > > >
> > > >
> > > >
> >
> >
> >