hi im trying to do a count if with two dates, but i need to add a left formula and the dates im counting has a time after it and the date im using as a reference doesnt.
Here my formula which would work if the dates im looking up didnt have time in the same cell.
=COUNTIFS($A:$A,H3,$B:$B,H3,$C:$C,I3)
I have also attached the example.
I know how to do left and right but i dont know where to put it within a formula.
Hi
Try
=SUMPRODUCT((INT(A6:A1000)=H3)*(C6:C1000=I3))
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Hi,
Try with SUMPRODUCT:
=SUMPRODUCT((INT($A$6:$A$40)=H3)*(INT($B$6:$B$40)=H3)*($C$6:$C$40=I3))
Regards
can i left be used as i can get my head around left, although i am going to look into that INT
Ive never seen INT before, i will need to add a few more columns to the formula as well, will that be the best formula to use?
What does INT mean?
Many thanks
INT() returns only the integer portion of a number. Since dates in Excel use the day as its base unit, INT(date) will return the days portion only.
Regards
When the dates have times you effectively need to check that they are >= H3 and < H3+1 to check that they are some time in H3 so you can use COUNTIFS like this
=COUNTIFS($A:$A,">="&H3,$B:$B,">="&H3,$A:$A,"<"&H3+1,$B:$B,"<"&H3+1,$C:$C,I3)
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks