Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
steve alcock
 
Posts: n/a
Default 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


  #2   Report Post  
Jerry W. Lewis
 
Posts: n/a
Default

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


  #3   Report Post  
steve alcock
 
Posts: n/a
Default

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


.

  #4   Report Post  
 
Posts: n/a
Default

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


.

.

  #5   Report Post  
Jerry W. Lewis
 
Posts: n/a
Default

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

.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to use SUMIF to return sums between two values located in cells ScottBerger Excel Worksheet Functions 2 April 23rd 23 09:05 PM
Multiple Condition Sumif Formula momtoaj Excel Worksheet Functions 3 April 6th 05 04:06 PM
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function Oscar Excel Worksheet Functions 2 January 12th 05 12:01 AM
SUM(IF( Array to avoid #NUM! values Elijah Excel Worksheet Functions 7 November 21st 04 03:17 PM
Is it possible to specify multiple condition with SUMIF? Daniel Excel Worksheet Functions 1 November 17th 04 02:38 AM


All times are GMT +1. The time now is 08:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"