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?
> > >
> > >
> > >
>
>
>
Bookmarks