Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I need to solve this query:

  • Column A contains an array of dates
  • Column B contains daily entry of no / yes

This formula returns the number of occurrences for no:

=(COUNTIF(b1:b365,"no"))

I need to find the occurrences of no in the current month only. How can I do that?

share|improve this question

2 Answers 2

If you have Excel 2007 or later version try COUNTIFS, e.g.

=COUNTIFS(B1:B365,"no",A1:A365,">="&EOMONTH(TODAY(),-1)+1,A1:A365,"<"&EOMONTH(TODAY(),0)+1)

or you can use SUMPRODUCT in any version

=SUMPRODUCT((B1:B365="no")*(TEXT(A1:A365,"mmm-yy")=TEXT(TODAY(),"mmm-yy")))

share|improve this answer
    
thanks a lot it worked well for me –  mahendra Jadhav Jan 2 '13 at 21:55
    
by using this i made a formula to calculate monthly bill of milkman =CONCATENATE("Rs. ",(((TODAY())-(DATE(YEAR(TODAY()),MONTH(TODAY()),1)))-COUNTIFS(J6:J381,"a",I6:I3‌​81,">="&EOMONTH(TODAY(),-1)+1,I6:I381,"<"&EOMONTH(TODAY(),0)+1))*16) can you make it short again ? –  mahendra Jadhav Jan 2 '13 at 22:13
    
+1 for using the TEXT() function to simplify the month comparison. –1 for using yy instead of yyyy. –  Scott Jan 2 '13 at 22:19
    
@mahendra Jadhav, try this to shorten a little ="Rs. "&(DAY(TODAY())-1-COUNTIFS(J6:J381,"a",I6:I381,">="&EOMONTH(TODAY(),-1)+1,I6:I38‌​1,"<"&EOMONTH(TODAY(),0)+1))*16 –  barry houdini Jan 2 '13 at 22:50
    
@Scott - Thanks! I figured it was unlikely that there would be data from Jan 1913 (or even Jan 2113)! –  barry houdini Jan 2 '13 at 22:51

Barry Houdini has given you some great formulas to use. (I really like the SUMPRODUCT one.)

If you are open to another approach you can use a pivot table.

Set up your table like this:

enter image description here

Then choose "Options" and "Group Selection" like this:

enter image description here

You'll end up with this:

enter image description here

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.