Counting all records within a month based on a date

**Phlegon_of_Tralles** · 08-25-2023, 11:23 AM

Okay, so I have another one. I'm trying to count all the people who have a term date within a certain month. That month will change and will be listed as of the end of the month. So, for August, the date is August 31, 2023. I'm looking for everyone with an August 2023 date.

The formula I currently have is in G3. It's =COUNTIF(B:B,TEXT(G1,"MM/YYYY")) and it's not working. It's bringing back 1 (probably because it's seeing the 8/31/2023 record). It should bring back 3.

It's Friday and my brain isn't firing on all cylinders yet... What am I missing? I feel like it's something obvious.

**HansDouwe** · 08-25-2023, 11:42 AM

One way, please try:

Formula:

Please Login or Register  to view this content.

**FlameRetired** · 08-25-2023, 03:27 PM

Another way.

Formula:

Please Login or Register  to view this content.

**alansidman** · 08-25-2023, 03:37 PM

With Power Query, you can set a parameter as shown in cells G1:G2 for a table starting in A1. Your result is in F5

Excel 2016 (Windows) 64 bit

	A	B	D	F	G
1	Name	Date			Test Date
2	John	7/1/2023			August 31, 2023
3	Jake	8/15/2023
4	Jimmy	8/31/2023		Table1
5	Danny	8/1/2023	Count if EE in August	3

Sheet: Sheet1

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

**Phlegon_of_Tralles** · 08-25-2023, 04:08 PM

So, that works. The issue I'm having is that I'm trying to attach additional criteria to the formula. So, for instance, I want to add Vol as a criteria and corporate as another criteria.

I input this and it comes up with a value error:

=SUM(((YEAR('Term Data'!$J$2:$J$1700)=YEAR($M$4))*(MONTH('Term Data'!$J$2:$J$1700)=MONTH($M$4)))*('Term Data'!I2:I1700)="VOL")*('Term Data'!B2:B1700)="Corporate")

Edit: This is on a different worksheet.

**HansDouwe** · 08-25-2023, 04:21 PM

One try (without testing): The whole formula should between the brackets of SUM:

Formula:

Please Login or Register  to view this content.

If that does not work, please upload a sample sheet with the error

**Phlegon_of_Tralles** · 08-25-2023, 05:13 PM

I appreciate the help - it's producing a value error. I've duplicated (I think) the essential information on the workbook, attached.

This is the formula I used (in order to save the file attached, I had to accept the correction).

=SUM(((YEAR(Sheet2!B:B)=YEAR(G1))*(MONTH(Sheet2!B:B)=MONTH(G1))*(Sheet2!C:C)="VOL")*(Sheet2!D:D)="Corp"))

**FlameRetired** · 08-25-2023, 05:21 PM

This is same as my previous but with the conditions

Formula:

Please Login or Register  to view this content.

**Phlegon_of_Tralles** · 08-25-2023, 06:14 PM

@FalmeRetired - that worked beautifully. Thank you.

How the heck do you all learn how to do all these formulas?

**HansDouwe** · 08-25-2023, 07:39 PM

Or try

Formula:

Please Login or Register  to view this content.

Not all the brackets where correct placed and don't take the header in the formula.

**FlameRetired** · 08-31-2023, 04:12 PM

Originally Posted by Phlegon_of_Tralles

@FalmeRetired - that worked beautifully. Thank you.

How the heck do you all learn how to do all these formulas?

Sorry for the delay. I've been away for the last several days.

Glad it helps.

RE: "How we learn all these formulas."
I study the masters on this and other forums and propose solution when I think I have something that might be useful. I swear one of the fastest and most effective ways to learn a subject is to teach it. I have much to learn.

Counting all records within a month based on a date

LinkBack

Thread Tools

Rate This Thread

Display

Counting all records within a month based on a date

Re: Counting all records within a month based on a date

Re: Counting all records within a month based on a date

Re: Counting all records within a month based on a date

Re: Counting all records within a month based on a date

Re: Counting all records within a month based on a date

Re: Counting all records within a month based on a date

Re: Counting all records within a month based on a date

Re: Counting all records within a month based on a date

Re: Counting all records within a month based on a date

Re: Counting all records within a month based on a date

Thread Information

Users Browsing this Thread

Similar Threads

Counting Employees by Month Based on Hire Date Until Made Inactive

Counting records open as of end of month

[SOLVED] Counting & summing value based on date/month

[SOLVED] Counting records based on date and time.

Counting data based on a date period eg. Month

[SOLVED] Counting records within a month

Counting unique records based on date range

Bookmarks

Bookmarks

Posting Permissions