• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Countifs criteria with dates and time

M

murugaraj

New Member
The first column of my excel sheet contains the number of units (integers); the second column has the despatch date; and the third column contains the due date with time (e.g. 6/29/10 10:38). I'm interested in counting the number of units not yet despatched. That is:

count "the number of units" if the due date is "this" and if the despatch date is empty


If the despatch date is without the time element, the formula would be easier. Can someone help me?
 
Assuming the columns are in the order listed and the dates don't have times


This will do the job

=SUMIFS(A2:A10,B2:B10,"4/5/2010",C2:C10,"")


or if the "This" Date is in B13

=SUMIFS(A2:A10,B2:B10,B13,C2:C10,"")


Expand the length of the columns to suit
 
Btw,, while Hui's formula works when due date = this date, it may not work properly (not sure, not tested, so let me know if I am wrong) when you have time element in due date column.


Try this if that is the case


=SUMPRODUCT(A2:A10,--(INT(B2:B10)="4/5/2010")*(C2:C10=""))
 
Dear Chandoo and Hui,

Thanks for your help. My dates have times, so I can use Chandoo's suggestion.

However, when I tried, I got the sum wrong (it was 0 always!). I tried to find where I'm going wrong. I get a bizarre result at the following step:INT(C4:C10)="6/23/2010" is always FALSE--even when the range C4:C10 has "23 June" as an entry, of course with time element.

Moreover, what is the significance of using -- before the INT function?

(Of course, I tried both with and without --, only to get the same answer.)

Thanks in advance for your insights.
 
Murugaraj

Int returns the integer component of the number, in this case it will return the Date, stripping off the Time component

The problem with Chandoos suggestion is that Int will return a Date which is a Number but the second part "6/23/2010" is text


Try the following

=SUMPRODUCT(A2:A10,(INT(B2:B10)=DATE(2010,5,4))*(C2:C10=""))
 
Thanks Hui, I got the logic. I made a small change and used:

=SUMPRODUCT(A2:A10,((INT(B2:B10)=DATEVALUE("6/23/2010"))*(C4:C10=""))). And it worked just fine. Thanks again.

Murugaraj
 
You must log in or register to reply here.
Back
Top