Sumproduct between and

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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))
 
=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.
 
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.

=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.
Click to expand...
 
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.
 
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
 
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.

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.
Click to expand...
 
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.
 
That explains it--you never want to have a stale ODBC!

<gd&r>
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.
Click to expand...
 
Back
Top