SUMIF where values to be summed are formula

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?

Bob,

Thanks, I'll give it a go!

Tony

"Bob Phillips" wrote:

> Sorry I missed that you added some extra --, should be
>
> =SUMPRODUCT(--(A1:A1613="Saturday"),--(C1:C1613=--"08:15"),F1:F1613)
>
> but if column A is dates it should further be
>
> =SUMPRODUCT(--(TEXT(A1:A1613,"dddd")="Saturday"),--(C1:C1613=--"08:15"),F1:F
> 1613)
>
> or
>
> =SUMPRODUCT(--(WEEKDAY(A1:A1613)=6),--(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]...
> > Thanks, I see I missed a couple of -- but on adding it in, I'm still
> getting
> > #VALUE.
> >
> > The conditional sum wizard should do the job, I think I've now figured out
> > how to use it properly.
> >
> > I've read that SUMPRODUCT is the most powerful and flexible Excel
> function,
> > but I've got nowhere with it.
> >
> > Anyway, many thanks for all your help and advice.
> >
> > Regards
> >
> > Tony
> >
> > "Bob Phillips" wrote:
> >
> > > 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?
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Many thanks, I've tried it and it works just fine, BUT ........

It ONLY works without returning a #DIV! OR #vALUE! etc(or, rather, I can
only get it to work) if I manually retype the values in F1:F1613 into
adjacent cells and change the F1:F1613 ref to the column into which I've
copied the values, eg G1:g1613

There are a vast amount of values to convert from, so I could only really
use the formula if I can get these formula converted to values instead of
retyping them all.

Is there a Visual Basic program I could write or an Excel function that
would allow me to do this?

"Morrigan" wrote:

>
> Try this:
>
> =SUMPRODUCT(--(A1:A1613="Saturday"),--(C1:C1613=TIME(8,15,0)),F1:F1613)
>
> You can also replace TIME(8,15,0) with a reference cell
>
>
> Hope it helps.
>
>
>
>
> Zakynthos Wrote:
> > 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?
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=390517
>
>

Sorry I missed that you added some extra --, should be

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

but if column A is dates it should further be

=SUMPRODUCT(--(TEXT(A1:A1613,"dddd")="Saturday"),--(C1:C1613=--"08:15"),F1:F
1613)

or

=SUMPRODUCT(--(WEEKDAY(A1:A1613)=6),--(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]...
> Thanks, I see I missed a couple of -- but on adding it in, I'm still
getting
> #VALUE.
>
> The conditional sum wizard should do the job, I think I've now figured out
> how to use it properly.
>
> I've read that SUMPRODUCT is the most powerful and flexible Excel
function,
> but I've got nowhere with it.
>
> Anyway, many thanks for all your help and advice.
>
> Regards
>
> Tony
>
> "Bob Phillips" wrote:
>
> > 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?
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Thanks, I see I missed a couple of -- but on adding it in, I'm still getting
#VALUE.

The conditional sum wizard should do the job, I think I've now figured out
how to use it properly.

I've read that SUMPRODUCT is the most powerful and flexible Excel function,
but I've got nowhere with it.

Anyway, many thanks for all your help and advice.

Regards

Tony

"Bob Phillips" wrote:

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

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

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

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

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

Bob,

Thanks, I'll give it a go!

Tony

"Bob Phillips" wrote:

> Sorry I missed that you added some extra --, should be
>
> =SUMPRODUCT(--(A1:A1613="Saturday"),--(C1:C1613=--"08:15"),F1:F1613)
>
> but if column A is dates it should further be
>
> =SUMPRODUCT(--(TEXT(A1:A1613,"dddd")="Saturday"),--(C1:C1613=--"08:15"),F1:F
> 1613)
>
> or
>
> =SUMPRODUCT(--(WEEKDAY(A1:A1613)=6),--(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]...
> > Thanks, I see I missed a couple of -- but on adding it in, I'm still
> getting
> > #VALUE.
> >
> > The conditional sum wizard should do the job, I think I've now figured out
> > how to use it properly.
> >
> > I've read that SUMPRODUCT is the most powerful and flexible Excel
> function,
> > but I've got nowhere with it.
> >
> > Anyway, many thanks for all your help and advice.
> >
> > Regards
> >
> > Tony
> >
> > "Bob Phillips" wrote:
> >
> > > 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?
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Many thanks, I've tried it and it works just fine, BUT ........

It ONLY works without returning a #DIV! OR #vALUE! etc(or, rather, I can
only get it to work) if I manually retype the values in F1:F1613 into
adjacent cells and change the F1:F1613 ref to the column into which I've
copied the values, eg G1:g1613

There are a vast amount of values to convert from, so I could only really
use the formula if I can get these formula converted to values instead of
retyping them all.

Is there a Visual Basic program I could write or an Excel function that
would allow me to do this?

"Morrigan" wrote:

>
> Try this:
>
> =SUMPRODUCT(--(A1:A1613="Saturday"),--(C1:C1613=TIME(8,15,0)),F1:F1613)
>
> You can also replace TIME(8,15,0) with a reference cell
>
>
> Hope it helps.
>
>
>
>
> Zakynthos Wrote:
> > 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?
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=390517
>
>

Sorry I missed that you added some extra --, should be

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

but if column A is dates it should further be

=SUMPRODUCT(--(TEXT(A1:A1613,"dddd")="Saturday"),--(C1:C1613=--"08:15"),F1:F
1613)

or

=SUMPRODUCT(--(WEEKDAY(A1:A1613)=6),--(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]...
> Thanks, I see I missed a couple of -- but on adding it in, I'm still
getting
> #VALUE.
>
> The conditional sum wizard should do the job, I think I've now figured out
> how to use it properly.
>
> I've read that SUMPRODUCT is the most powerful and flexible Excel
function,
> but I've got nowhere with it.
>
> Anyway, many thanks for all your help and advice.
>
> Regards
>
> Tony
>
> "Bob Phillips" wrote:
>
> > 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?
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Thanks, I see I missed a couple of -- but on adding it in, I'm still getting
#VALUE.

The conditional sum wizard should do the job, I think I've now figured out
how to use it properly.

I've read that SUMPRODUCT is the most powerful and flexible Excel function,
but I've got nowhere with it.

Anyway, many thanks for all your help and advice.

Regards

Tony

"Bob Phillips" wrote:

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

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

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

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

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

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?

Bob,

Thanks, I'll give it a go!

Tony

"Bob Phillips" wrote:

> Sorry I missed that you added some extra --, should be
>
> =SUMPRODUCT(--(A1:A1613="Saturday"),--(C1:C1613=--"08:15"),F1:F1613)
>
> but if column A is dates it should further be
>
> =SUMPRODUCT(--(TEXT(A1:A1613,"dddd")="Saturday"),--(C1:C1613=--"08:15"),F1:F
> 1613)
>
> or
>
> =SUMPRODUCT(--(WEEKDAY(A1:A1613)=6),--(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]...
> > Thanks, I see I missed a couple of -- but on adding it in, I'm still
> getting
> > #VALUE.
> >
> > The conditional sum wizard should do the job, I think I've now figured out
> > how to use it properly.
> >
> > I've read that SUMPRODUCT is the most powerful and flexible Excel
> function,
> > but I've got nowhere with it.
> >
> > Anyway, many thanks for all your help and advice.
> >
> > Regards
> >
> > Tony
> >
> > "Bob Phillips" wrote:
> >
> > > 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?
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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?

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

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?

Many thanks, I've tried it and it works just fine, BUT ........

It ONLY works without returning a #DIV! OR #vALUE! etc(or, rather, I can
only get it to work) if I manually retype the values in F1:F1613 into
adjacent cells and change the F1:F1613 ref to the column into which I've
copied the values, eg G1:g1613

There are a vast amount of values to convert from, so I could only really
use the formula if I can get these formula converted to values instead of
retyping them all.

Is there a Visual Basic program I could write or an Excel function that
would allow me to do this?

"Morrigan" wrote:

>
> Try this:
>
> =SUMPRODUCT(--(A1:A1613="Saturday"),--(C1:C1613=TIME(8,15,0)),F1:F1613)
>
> You can also replace TIME(8,15,0) with a reference cell
>
>
> Hope it helps.
>
>
>
>
> Zakynthos Wrote:
> > 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?
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
> --
> Morrigan
> ------------------------------------------------------------------------
> Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
> View this thread: http://www.excelforum.com/showthread...hreadid=390517
>
>

Sorry I missed that you added some extra --, should be

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

but if column A is dates it should further be

=SUMPRODUCT(--(TEXT(A1:A1613,"dddd")="Saturday"),--(C1:C1613=--"08:15"),F1:F
1613)

or

=SUMPRODUCT(--(WEEKDAY(A1:A1613)=6),--(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]...
> Thanks, I see I missed a couple of -- but on adding it in, I'm still
getting
> #VALUE.
>
> The conditional sum wizard should do the job, I think I've now figured out
> how to use it properly.
>
> I've read that SUMPRODUCT is the most powerful and flexible Excel
function,
> but I've got nowhere with it.
>
> Anyway, many thanks for all your help and advice.
>
> Regards
>
> Tony
>
> "Bob Phillips" wrote:
>
> > 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?
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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

Thanks, I see I missed a couple of -- but on adding it in, I'm still getting
#VALUE.

The conditional sum wizard should do the job, I think I've now figured out
how to use it properly.

I've read that SUMPRODUCT is the most powerful and flexible Excel function,
but I've got nowhere with it.

Anyway, many thanks for all your help and advice.

Regards

Tony

"Bob Phillips" wrote:

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

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

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