# Get count of items based on certain dates

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?

-

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")))`

-
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:

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

You'll end up with this:

-