Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Multiple Condition Sumif Formula | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
SUM(IF( Array to avoid #NUM! values | Excel Worksheet Functions | |||
Is it possible to specify multiple condition with SUMIF? | Excel Worksheet Functions |