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