Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .