COUNTIF using range from TEXT

[johnjensen75]

Using this format I am able to pull a single month in text format from a date such as 01/01/2022.
I am trying to do a COUNTIF in a date range in that format, but by searching by the month, example "February"
=(TEXT(A2,"MMMM"))

my date range is in A, my month is in C1
=COUNTIF((text(A:A,"mmmm")),C1)

I realize it would be easier to just add a month only collumn, but this is for my supervisor and not allowed to change the sheet.

Any help would be appreciated, thanks.

 

[Joe4]

Welcome to the Board!

I think it would be easier to use COUNTIFS, and simply return all records that fall between the month start and end dates.
Something like this: Excel formula: Summary count by month with COUNTIFS | Exceljet

Also note the EOMONTH function which will dynamically return the end date of any month.

How to use the Excel EOMONTH function | Exceljet

How to use the Excel EOMONTH function to Get last day of month n months in future or past.

exceljet.net

 

[JamesCanale]

Joe4 is right, but if you are really constrained in your solution - this will work. However it's really slow with A:A. If you could limit that substantially, it would be better.

MrExcelPlayground11.xlsx
	A	B	C	D	E
1	1/15/2022		January		2
2	2/18/2022
3	3/4/2022
4	2/20/2022
5	1/5/2022
Sheet14

Cell Formulas
Range	Formula
E1	E1	=SUMPRODUCT(--(TEXT(A:A,"mmmm")=C1),--(A:A<>""))

 

[johnjensen75]

Joe4 is right, but if you are really constrained in your solution - this will work. However it's really slow with A:A. If you could limit that substantially, it would be better.

MrExcelPlayground11.xlsx
	A	B	C	D	E
1	1/15/2022		January		2
2	2/18/2022
3	3/4/2022
4	2/20/2022
5	1/5/2022
Sheet14

Cell Formulas
Range	Formula
E1	E1	=SUMPRODUCT(--(TEXT(A:A,"mmmm")=C1),--(A:A<>""))

that worked very well, thanks.

 

[johnjensen75]

Joe4 is right, but if you are really constrained in your solution - this will work. However it's really slow with A:A. If you could limit that substantially, it would be better.

could you please explain that formula a little for me?

 

[JamesCanale]

sumproduct is old-school for a 365 user - but it deals with a function on the input range better... it would also be a bit easier if you could put "2" in C1, instead of "February" - but...

It converts column A into a sequence of month texts. If you could make it A2:A1000 or something, instead of all of A:A - it would go much much faster when you update.
Then it checks if that text = C1. You already knew all of this.

So what you'll get is a sequence of TRUE; FALSE; TRUE; TRUE; FALSE;... with that part of the expression. So, in excel, when you do math on TRUE or FALSE, it turns into 1 or 0. So adding zero, or multiplying by 1 or in this case multiplying by -1 twice ("--") turns all of those TRUEs and FALSEs to 1;0;1;1;0. It's summing that up.

The second expression deals with the blanks in column A. If there is nothing in a cell in column A, the TEXT function will take it as January 0, 1900 and return a long list of "January"s - artificially adding to your January count. So I have it zero out the blank cells with the --(A:A<>"") line, so multiplying the fake 1's from the blanks by 0.

I might do something with a LET statement if it was my sheet.

MrExcelPlayground11.xlsx
	A	B	C	D	E
1	1/15/2022		February		2
2	2/18/2022
3	3/4/2022
4	2/20/2022
5	1/5/2022
Sheet14

Cell Formulas
Range	Formula
E1	E1	=LET(a,IF(A1:A1000<>"",TEXT(A1:A1000,"mmmm"),0),SUM(IF(a=C1,1,0)))