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

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 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

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

=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

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

I have taken the columns and formated them to dates dd/mm/yy on both pages,
the first page is an ODBC link. Could that be the problem? I will give a
try by manually inputing dates on the linked page.

"Dave Peterson" wrote:

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

I manually entered the date and checked the formating and still could not get
a result, just blanks. Is there another way to format the column? Or what
else could I look at? Thanks

"Dave Peterson" wrote:

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

The formula doesn't look at the way the values are formatted. It looks at the
value in the cells.

Maybe some of your dates are really in dmy order instead of mdy. Or maybe those
dates are really text (that look like dates).

I'd format the date cells in an unambiguous format (just temporarily):
mmmm dd, yyyy

And see if you have any cells that don't change what they display.


Arlene wrote:

I have taken the columns and formated them to dates dd/mm/yy on both pages,
the first page is an ODBC link. Could that be the problem? I will give a
try by manually inputing dates on the linked page.

"Dave Peterson" wrote:

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


--

Dave Peterson

Thanks Dave for your patience. I have it working, for some reason I just
needed to refresh my ODBC and it started to work well.

"Dave Peterson" wrote:

The formula doesn't look at the way the values are formatted. It looks at the
value in the cells.

Maybe some of your dates are really in dmy order instead of mdy. Or maybe those
dates are really text (that look like dates).

I'd format the date cells in an unambiguous format (just temporarily):
mmmm dd, yyyy

And see if you have any cells that don't change what they display.


Arlene wrote:

I have taken the columns and formated them to dates dd/mm/yy on both pages,
the first page is an ODBC link. Could that be the problem? I will give a
try by manually inputing dates on the linked page.

"Dave Peterson" wrote:

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


--

Dave Peterson

That explains it--you never want to have a stale ODBC!

<gd&r

Arlene wrote:

Thanks Dave for your patience. I have it working, for some reason I just
needed to refresh my ODBC and it started to work well.

"Dave Peterson" wrote:

The formula doesn't look at the way the values are formatted. It looks at the
value in the cells.

Maybe some of your dates are really in dmy order instead of mdy. Or maybe those
dates are really text (that look like dates).

I'd format the date cells in an unambiguous format (just temporarily):
mmmm dd, yyyy

And see if you have any cells that don't change what they display.


Arlene wrote:

I have taken the columns and formated them to dates dd/mm/yy on both pages,
the first page is an ODBC link. Could that be the problem? I will give a
try by manually inputing dates on the linked page.

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson