closed workbook formula half solved!

[catten]

Hi all,

Being a novice at Excel and producing an excel project for the boss, I've found out that some of the imported data doesnt work if the source workbook isnt open on some of the info needed.

This affects data for 13 staff on 12 (monthly) sheets which I have tried to fix for a week solid with no joy.

I need to know how many times a person's name in column 'D' appears for a particular given month from the dates in column 'A' on the closed workbook.

For January I was using -

=COUNTIFS('[salesmaseter.xlsx]sales2023'!$D:$D,"Shaun",'[salesmaster.xlsx]sales2023'!$A:$A,"<02/01/2023")

for February I was using a range of dates rather than just 'less than' like for February-

=COUNTIFS('[salesmaster.xlsx]sales2023'!$D:$D,"Shaun",'[salesmaster.xlsx]sales2023'!$A:$A,">01/31/2023", '[salesmaster.xlsx]sales2023'!$A:$A, "<03/01/2023")

and so on.

I have managed to solve half the issue with what i think is right -

=COUNT(IFS('[salesmaster.xlsx]sales2023'!$D:$D="shaun",1))

which counts the total for the year, which is 18. But I need it for just a given month so for this example, in January by either doing a 'less than' February date range or or from 01/01/23 to the end of January if that's better. I am just struggling with the second part of the formula and cannot not work it out as whatever I do returns 0 or 18 and not the correct figure which is 10.

For February onwards, I will need a greater than and and less than formula too.

Any ideas what the second part of this formula needs to be to get all entries with 'Shaun' in column D for the date range of January in column A?

Would love help on this!

many thanks

 

[Fluff]

Hi & welcome to MrExcel.
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)