Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
bkclark
 
Posts: n/a
Default How do I create a formula in Excel that will countif or sumif bef.

How do I create a formula in Excel that will countif or sum if before or
after a specific date? Whenever I put the actual date in the formula it
works, but when I reference to the cell the date is in it returns 0.
  #2   Report Post  
Dave R.
 
Posts: n/a
Default

If the cell contains a date, it will contain some number like 37756 or
something. If your formula with hand written date is

=SUMIF(G3:G5,""&"10/1/2002",H3:H5)

try putting 10/1/2002 (ensure Excel knows it's a date. you can format it as
a number to be sure) in H1, then you can use

=SUMIF(G3:G5,""&H1,H3:H5)


"bkclark" wrote in message
...
How do I create a formula in Excel that will countif or sum if before or
after a specific date? Whenever I put the actual date in the formula it
works, but when I reference to the cell the date is in it returns 0.



  #3   Report Post  
RagDyeR
 
Posts: n/a
Default

Try this:

=COUNTIF(A1:A20,""&C1)

With the date in C1.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"bkclark" wrote in message
...
How do I create a formula in Excel that will countif or sum if before or
after a specific date? Whenever I put the actual date in the formula it
works, but when I reference to the cell the date is in it returns 0.


  #4   Report Post  
N Harkawat
 
Posts: n/a
Default

I use the following and get the correct result
=COUNTIF(B4:B7,""&B10)
where B10 holds the date

"bkclark" wrote in message
...
How do I create a formula in Excel that will countif or sum if before or
after a specific date? Whenever I put the actual date in the formula it
works, but when I reference to the cell the date is in it returns 0.



  #5   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi Dave
just as a note: This won't work in some/most European countries. If you
want a hardcoded string better to use the ISO date format:
=SUMIF(G3:G5,""&"2002-10-1",H3:H5)

--
Regards
Frank Kabel
Frankfurt, Germany

"Dave R." schrieb im Newsbeitrag
...
If the cell contains a date, it will contain some number like 37756

or
something. If your formula with hand written date is

=SUMIF(G3:G5,""&"10/1/2002",H3:H5)

try putting 10/1/2002 (ensure Excel knows it's a date. you can format

it as
a number to be sure) in H1, then you can use

=SUMIF(G3:G5,""&H1,H3:H5)


"bkclark" wrote in message
...
How do I create a formula in Excel that will countif or sum if

before or
after a specific date? Whenever I put the actual date in the

formula it
works, but when I reference to the cell the date is in it returns

0.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 2 May 13th 23 11:46 AM
Creating an EXCEL COUNTIF formula for a range of values Pat Walsh Excel Discussion (Misc queries) 5 January 21st 05 03:57 PM
Excel 2003 - Formula result shows as 0:00 Serena Excel Worksheet Functions 4 November 11th 04 11:18 PM
How do I create a multi formula IF function in Excel? Wazza Excel Worksheet Functions 8 November 8th 04 10:25 PM
how do i create a blank cell in excel using a formula Tosh Excel Worksheet Functions 1 November 2nd 04 05:22 PM


All times are GMT +1. The time now is 10:30 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"