count the number of cells that contain a specific date in a specific year from 2 different spreeadsheets

[alghazaly]

Hello ,

I have a question

I am trying to create a formula that is excuted as following:

I am working on 2 spreadsheets(tabs) named indicators and FNCI maj :

in spreadsheet 1 FNCI maj there are multiple dates from 2017 till 2021

in spreadsheet 2 indicators there are indicators where i am applying the formula

i want a function that could count the number of cells that contain the month march and are in year 2021.

So basically counting all the cells that contain a given month in a given year ,

i've been trying but without success

i tried using this formula

=SUMPRODUCT((MONTH('FNCI maj'!B2:B404)=3)*(YEAR('FNCI maj'!B2:B404)=2021))

Thanks for your help

 

[Fluff]

Hi & welcome to MrExcel.
In what way doesn't it work?

 

[smozgur]

@alghazaly - Welcome to the board.

Fluff is right, I just filled some date values in a worksheet (column B) and named like your worksheet, then copied your formula. It works perfectly.

See the sample below:

Book1
	B	C	D
1	Date		Result for 3/2021
2	3/5/21		2
3	3/7/21
4	4/2/21
5	5/1/21
6	5/3/21
FNCI maj

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT((MONTH('FNCI maj'!B2:B404)=3)*(YEAR('FNCI maj'!B2:B404)=2021))

That's why we use XL2BB by the way. You can copy and paste it into your worksheet to test it.
Or share a sample range to show us how it doesn't work.

 

[alghazaly]

it simply gives mes an error message...

 

[Fluff]

Do you have any text values in the 'FNCI maj'!B2:B404 range, or formulae that return "" or #VALUE!

 

[alghazaly]

indeed! there was one row with a text format , it works perfectly now! thanks for the hint!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[smozgur]

indeed! there was one row with a text format , it works perfectly now! thanks for the hint!

Glad to hear you got the solution.

Please mark the post that solved your problem by clicking on the check mark icon next to that post next time as I did that for you in this question. It will help future readers to find the solution faster.
Thanks.