I:DV is 118 columns, so it is unclear what you want to happen with the
last 18.
If you make the ranges contain the right number of cells (for instance
G1:G118), you still need to make the shapes correspond to the shape of
the area you want to sum. You could then use
=SUMPRODUCT((I1:DV1=1)*TRANSPOSE(G1:G118=G2),I5:DV 5)
which (unlike my previous formula) must be array entered
(Ctrl-Shift-Enter) to work properly (because of TRANSPOSE).
Since you have to array enter it anyway, you might as soon use
=SUM((I1:DV1=1)*TRANSPOSE(G1:G118=G2)*I5:DV5)
and reserve SUMPRODUCT for uses where array entry is not required.
All of these formulas make use of the fact that in Excel FALSE coerces
to 0 and TRUE coerces to 1, so boolean conditions can be combined using
+ for OR and * for AND.
Jerry
steve alcock wrote:
Hi Jerr,
sorry if I was not clear it was very early this morning so
here goes :
cells i1 to dv1 hold the period values from 1 to 12
cells i5 ( 6,7,8,9, etc ) hold money values
cells g1 to cell g100 hold and ID 1,2,3,4 etc
now :
sumif(i1:idv1,g1:g100,"="&g2,i5:dv5)
finds the period value and ( 1 ) returns the value for the
range ( 680 )BUT what I want to do is bring in a second
condition :
sumif(and(i1:idv100,"="&g2,g1:g100,"="g2),i1:dv100 ))
hoping that it finds the period ( 1 ) and then looks for
the suite to look at to sum ( 2 ) but there is an error in
my formula and I have tried always to get excel to accept
this situation, is it possible ?
I hope the above explains a little better
regards
steve
-----Original Message-----
I do not find your description at all clear, but guessing
at your meaning,
=SUMPRODUCT((I1:DV1=1)*(G2:G100=2),I2:DV100)
will sum only those cells from I2:DV100 for which the
corresponding
cells in I1:DV1 equal 1 and the corresponding cells in
G2:G100 equal 2.
Jerry
steve alcock wrote:
hi all,
I need some advice : below is my problem sheet, I want
to
sumif I 1 to dv 1 = 1( period ) and g1 to g100 =2 (
which
room ) to get a period total for the period and suite
using one condition of sum if works fine but when I put
in
the second condition it errors :
is it possible to achieve this senario ?
1 1 1
week week
1 1 2
03/04/05 09/04/05 15/04/05
1 suite 1 170 170
2 suite 2 132.5 132.5 132.5
3 suite 3 70 70 70
4 suite 4 20 20 20
5 suite 5 10 20 20
6 suite 6 30 30 30
many thanks
steve
.
|