| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumproduct between and
Is it possible to sumproduct((a1:a50=D30)*(b1:b50 between(01/06/07)
and(30/06/07))*c1:c50) I am trying to get a sum of a column based on dates as well as other criteria. Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct between and
Just keep adding them to the criteria:
=sumproduct((a1:a50=d30) *(b1:b50=date(2007,6,1)) *(b1:b50<=date(2007,6,30) *(c1:c50)) Or since you want a single month: =sumproduct((a1:a50=d30) *(text(b1:b50,"yyyymm")="200706") *(c1:c50)) I guess it depends on what happens on the June 1 and June 30, though. You may not want = and <= in that first formula. Arlene wrote: Is it possible to sumproduct((a1:a50=D30)*(b1:b50 between(01/06/07) and(30/06/07))*c1:c50) I am trying to get a sum of a column based on dates as well as other criteria. Thanks -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct between and
Dave is there anyway I can reference cells for the month either in < or so
someone only has to change those two cells or one (month only) to get the formulas to work. I have tried*(Input a5:A55<=date(a1))*(Input a5:a55=date(a2)) and formated the cells to the same as the input sheet values but just get an error message. Thanks "Dave Peterson" wrote: Just keep adding them to the criteria: =sumproduct((a1:a50=d30) *(b1:b50=date(2007,6,1)) *(b1:b50<=date(2007,6,30) *(c1:c50)) Or since you want a single month: =sumproduct((a1:a50=d30) *(text(b1:b50,"yyyymm")="200706") *(c1:c50)) I guess it depends on what happens on the June 1 and June 30, though. You may not want = and <= in that first formula. Arlene wrote: Is it possible to sumproduct((a1:a50=D30)*(b1:b50 between(01/06/07) and(30/06/07))*c1:c50) I am trying to get a sum of a column based on dates as well as other criteria. Thanks -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct between and
Put the smaller date in A1 and the larger date in A2. And make sure you enter
real dates in A1 and A2. =sumproduct((a1:a50=d30) *(b1:b50=a1) *(b1:b50<=a2) *(c1:c50)) Arlene wrote: Dave is there anyway I can reference cells for the month either in < or so someone only has to change those two cells or one (month only) to get the formulas to work. I have tried*(Input a5:A55<=date(a1))*(Input a5:a55=date(a2)) and formated the cells to the same as the input sheet values but just get an error message. Thanks "Dave Peterson" wrote: Just keep adding them to the criteria: =sumproduct((a1:a50=d30) *(b1:b50=date(2007,6,1)) *(b1:b50<=date(2007,6,30) *(c1:c50)) Or since you want a single month: =sumproduct((a1:a50=d30) *(text(b1:b50,"yyyymm")="200706") *(c1:c50)) I guess it depends on what happens on the June 1 and June 30, though. You may not want = and <= in that first formula. Arlene wrote: Is it possible to sumproduct((a1:a50=D30)*(b1:b50 between(01/06/07) and(30/06/07))*c1:c50) I am trying to get a sum of a column based on dates as well as other criteria. Thanks -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct between and
=SUMPRODUCT(('YTD MACH HRS'!$I$5:$I$65536='PHASE HRS MONTHLY'!$C7)*('YTD MACH
HRS'!$G$5:$G$65536='PHASE HRS MONTHLY'!$B8)*('YTD MACH HRS'!$B$5:$B$65536='PHASE HRS MONTHLY'!$A$2)*'YTD MACH HRS'!$B$5:$B$65536<='PHASE HRS MONTHLY'!$A$3)*'YTD MACH HRS'!$D$5:$D$65536 For some reason it doesn't seem to want to work a2 and a3 are date formated to exactly waht is in YTD Mach hrs b5:b65536. Can you see what I am doing wrong? Thanks I really appreciate it. "Dave Peterson" wrote: Put the smaller date in A1 and the larger date in A2. And make sure you enter real dates in A1 and A2. =sumproduct((a1:a50=d30) *(b1:b50=a1) *(b1:b50<=a2) *(c1:c50)) Arlene wrote: Dave is there anyway I can reference cells for the month either in < or so someone only has to change those two cells or one (month only) to get the formulas to work. I have tried*(Input a5:A55<=date(a1))*(Input a5:a55=date(a2)) and formated the cells to the same as the input sheet values but just get an error message. Thanks "Dave Peterson" wrote: Just keep adding them to the criteria: =sumproduct((a1:a50=d30) *(b1:b50=date(2007,6,1)) *(b1:b50<=date(2007,6,30) *(c1:c50)) Or since you want a single month: =sumproduct((a1:a50=d30) *(text(b1:b50,"yyyymm")="200706") *(c1:c50)) I guess it depends on what happens on the June 1 and June 30, though. You may not want = and <= in that first formula. Arlene wrote: Is it possible to sumproduct((a1:a50=D30)*(b1:b50 between(01/06/07) and(30/06/07))*c1:c50) I am trying to get a sum of a column based on dates as well as other criteria. Thanks -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sumproduct between and
What are the values in those cells? Are you sure that they're dates--not just
text that look like dates? It may help if you shink up the range (rows 5:10) and plop in some test data once more. Arlene wrote: =SUMPRODUCT(('YTD MACH HRS'!$I$5:$I$65536='PHASE HRS MONTHLY'!$C7)*('YTD MACH HRS'!$G$5:$G$65536='PHASE HRS MONTHLY'!$B8)*('YTD MACH HRS'!$B$5:$B$65536='PHASE HRS MONTHLY'!$A$2)*'YTD MACH HRS'!$B$5:$B$65536<='PHASE HRS MONTHLY'!$A$3)*'YTD MACH HRS'!$D$5:$D$65536 For some reason it doesn't seem to want to work a2 and a3 are date formated to exactly waht is in YTD Mach hrs b5:b65536. Can you see what I am doing wrong? Thanks I really appreciate it. "Dave Peterson" wrote: Put the smaller date in A1 and the larger date in A2. And make sure you enter real dates in A1 and A2. =sumproduct((a1:a50=d30) *(b1:b50=a1) *(b1:b50<=a2) *(c1:c50)) Arlene wrote: Dave is there anyway I can reference cells for the month either in < or so someone only has to change those two cells or one (month only) to get the formulas to work. I have tried*(Input a5:A55<=date(a1))*(Input a5:a55=date(a2)) and formated the cells to the same as the input sheet values but just get an error message. Thanks "Dave Peterson" wrote: Just keep adding them to the criteria: =sumproduct((a1:a50=d30) *(b1:b50=date(2007,6,1)) *(b1:b50<=date(2007,6,30) *(c1:c50)) Or since you want a single month: =sumproduct((a1:a50=d30) *(text(b1:b50,"yyyymm")="200706") *(c1:c50)) I guess it depends on what happens on the June 1 and June 30, though. You may not want = and <= in that first formula. Arlene wrote: Is it possible to sumproduct((a1:a50=D30)*(b1:b50 between(01/06/07) and(30/06/07))*c1:c50) I am trying to get a sum of a column based on dates as well as other criteria. Thanks -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
| sumproduct help | Excel Worksheet Functions | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
| sumproduct help | Excel Worksheet Functions | |||
Hybrid Mode
