Good morning,
I am trying to come up with a formula. I have dates in rows 1-86. in 88 and 89 I want to put the count of dates that fall into Q1 and Q2 respectively. I have an example below. I think all I need is a table or something showing all 2013 dates converted to numberical values so I can plug them into a countif formula, but not sure.
thanks!
Book1.xlsx
Assuming Quarter 1 = Jan 1st to March 31st try this formula
=SUMPRODUCT(0+(TEXT(B1:B86,"mmm;;")={"jan","feb","mar"}))
change the month names for quarter 2
It's not year specific......
Audere est facere
=countifs($b$1:$b$86, ">=" & --"1/1/13", $b$1:$b$86, "<=" & --"3/31/13")
Entia non sunt multiplicanda sine necessitate
Try for Q1.
=SUMPRODUCT((B1:B86>=41275)*(B1:B86<=41364))
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.
Thank you! This works. By chance, would it be too difficult to explain to me how this formula works? I'm not too familiar with array formulas.
Thanks,
No one of the above formulas is ARRAY formula(meaning no need to confirm as an array formula)... Which one you like to explain?..I'm not too familiar with array formulas.
=SUMPRODUCT(0+(TEXT(B1:B86,"mmm;;")={"jan","feb","mar"}))
This is the formula that worked well that i would like more explanation on.
The 2 really Excel Guru that offered 2 different suggestion will also confirm to you that:...This is the formula that worked well that i would like more explanation on.
1) ALL 3 formulas give the same result.
2)It's better to go with COUNTIFS suggestion as in fact SUMPRODUCT is an ARRAY formula and it is much more faster than SUMPRODUCT.
Depents of your date format you could try it in this way.
=COUNTIFS($B$1:$B$86, ">=" & --"01/01/2013", $B$1:$B$86, "<=" & --"31/03/13")
THanks for the advice!
Here's another one...
Entered in B88 and copied down:
=SUMPRODUCT(--(B$1:B$86<>""),--(LOOKUP(MONTH(B$1:B$86),{0,4,7,10},"Q"&{1,2,3,4})=A88))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
shg,
Thank you for the formula. I was wondering if you could tell me what the "& --" does? I am not familar with that expression.
Thanks,
It coerces the string that looks like a date into an Excel serial date (i.e., a number) in preparation for comparing it to other dates.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks