Sumif....

B

Bob Barnes

I'm an Access Programmer...

A co-worker asked why this doesn't work - it gives "0".

=SUMIF(C27:C38,"<01/04/2006 and >11/17/2005",D27:D38)

The...
"<01/04/2006 and >11/17/2005"...doesn't work

"<01/04/2006"...does work

TIA - Bob
 
B

Bob Phillips

You reckon?

=SUMPRODUCT(--(C27:C38<--"2006-01-04"),--(C27:C38>--"2005-11-17"),D27:D38)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
R

Ron Coderre

Try one of these:

=SUMIF(C27:C38,">11/17/2005",D27:D38)-SUMIF(C27:C38,">=1/4/2006",D27:D38)

or...a bit shorter (and a bit less intuitive)
=SUM(SUMIF(C27:C38,{">11/17/2005",">=1/4/2006"},D27:D38)*{1,-1})

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
T

T. Valko

Try this:

=SUMIF(C27:C38,">2005/11/17",D27:D38)-SUMIF(C27:C38,">=2006/1/4",D27:D38)

Better to use cells to hold the date variables:

A27 = 11/17/2005
A28 = 1/4/2006

=SUMIF(C27:C38,">"&A27,D27:D38)-SUMIF(C27:C38,">="&A28,D27:D38)

Biff
 
B

Bob Barnes

Thanks guys...

Ron Coderre said:
Try one of these:

=SUMIF(C27:C38,">11/17/2005",D27:D38)-SUMIF(C27:C38,">=1/4/2006",D27:D38)

or...a bit shorter (and a bit less intuitive)
=SUM(SUMIF(C27:C38,{">11/17/2005",">=1/4/2006"},D27:D38)*{1,-1})

Does that help?
***********
Regards,
Ron

XL2002, WinXP
Click to expand...
 
D

Dave Peterson

It always scares me when I see dates used like this.

Maybe(!) I'm overly cautious, but I'd use:

=SUMIF(C27:C38,">"&date(2005,11,17),D27:D38)
-SUMIF(C27:C38,">="&date(2006,1,4),D27:D38)
 
B

Bob Barnes

Thanks guys

Bob Phillips said:
You reckon?

=SUMPRODUCT(--(C27:C38<--"2006-01-04"),--(C27:C38>--"2005-11-17"),D27:D38)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
Click to expand...
 
R

Ron Coderre

I agree....I keep *meaning* to use yyyy/mm/dd or even yyyy-mm-dd
but it keeps slipping my mind...
especially, when I'm editing a posted formula.

Feel free to keep reminding me, though.
It's bound to sink in eventually. <vbg>
***********
Regards,
Ron

XL2002, WinXP
 
B

Bob Barnes

Another - thank you.

T. Valko said:
Try this:

=SUMIF(C27:C38,">2005/11/17",D27:D38)-SUMIF(C27:C38,">=2006/1/4",D27:D38)

Better to use cells to hold the date variables:

A27 = 11/17/2005
A28 = 1/4/2006

=SUMIF(C27:C38,">"&A27,D27:D38)-SUMIF(C27:C38,">="&A28,D27:D38)

Biff
Click to expand...
 
B

Bob Barnes

Thank you.

Dave Peterson said:
It always scares me when I see dates used like this.

Maybe(!) I'm overly cautious, but I'd use:

=SUMIF(C27:C38,">"&date(2005,11,17),D27:D38)
-SUMIF(C27:C38,">="&date(2006,1,4),D27:D38)
Click to expand...
 
Top