sumif on more than one condition
 

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

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

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

.

wrong.............


-----Original Message-----
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,"="&h2,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,"="&h2),i1:dv10 0))

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

.

.

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

.