How to give result in a cell based on monthly/yearly conditions?

Hi all,

Hope all are well.

Need a help in formulae in sheet PR Availability under column C.

The existing formula needs to be added with one more condition such that if Name in Column B in sheet PR Availability appears in column I of sheet PR Records more than 20 times in a month in a year, then show Not Available

Date to be considered as in column O.

Can someone please help

where is the date in the PR Availability sheet to use - i'm assuming the PR Records will be much longer and have various dates in
and which column of dates - column K ???

you should be able to add
=COUNTIFS('PR Records'!$I$3:$I$11,B4,'PR Records'!$K$3:$K$11,">="&DATE(YEAR($F$1),MONTH($F$1),DAY(1)),'PR Records'!$K$3:$K$11,"<="&EOMONTH(F1,1))
where i have added F1 as a date for the report to use
that will count the instances of that name in the month
then to if you want to exclude over 20 - just add >20
=COUNTIFS('PR Records'!$I$3:$I$11,B4,'PR Records'!$K$3:$K$11,">="&DATE(YEAR($F$1),MONTH($F$1),DAY(1)),'PR Records'!$K$3:$K$11,"<="&EOMONTH(F1,1))>20

but no date to tell what month to use

As mentioned in 1st post, dates are to be considered from column O in sheet "PR Records"

missed that
so
=COUNTIFS('PR Records'!$I$3:$I$11,B4,'PR Records'!$K$3:$K$11,">="&DATE(YEAR($F$1),MONTH($F$1),DAY(1)),'PR Records'!$K$3:$K$11,"<="&EOMONTH($F$1,1))>20
becomes
with O and can change the rows to however many needed
=COUNTIFS('PR Records'!$I$3:$I$100,B4,'PR Records'!$O$3:$O$100,">="&DATE(YEAR($F$1),MONTH($F$1),DAY(1)),'PR Records'!$O$3:$O$100,"<="&EOMONTH($F$1,1))>20
Add to the OR - for status needed -

But where is the date criteria to use in "PR Availability" - i have used F1 in the attached as the date of the report and that is then the month and year for the criteria
see results in column K

Date is to be used from sheet PR Records.

Sorry for the confusion, let me explain in detail.

Column C in sheet "PR Availability" should show as Not Available if below conditions are met, else Available

1. if D4 (PR Availability) = Do Not Disturb

2. If E4 (PR Availability) = Out of Office

3. If name under column B in sheet "PR Availability" appears under column I in sheet "PR Records" and has status as Pending more than 1 in count under column N (PR Availability)

4. If name under column B in sheet "PR Availability" appears under column I in sheet "PR Records" and has status as Completed more than 1 in count under column N (PR Availability) in a single day where the date is considered from column P (hidden)

All the above conditions are existing in current formula i.e. below one

Formula:

Please Login or Register  to view this content.

Now in addition to above conditions, below is also required

5. If name under Column B in sheet "PR Availability" appears in column I in sheet "PR Records" more than 20 times in a month in a year, for example (Feb 2024) where date to be considered is from column O or P (hidden).

Hope i was able to explain.

yep, thanks - thats would i thought you wanted - i was just struggling with how to count the dates

if you have months say for 2023 and 2024 year in pr records
and say only JAN 23 had more than 20 entries for one name

so the O is hidden in the "PR availability" sheet and thats what is looked up in sheet "PR records" for that month and year

I4 in PR availability is blank - what happens then

sorry i'm not getting it for you .........

=COUNTIFS('PR Records'!$I$3:$I$100,B4,'PR Records'!$O$3:$O$100,">="&DATE(YEAR($I4),MONTH($I4),DAY(1)),'PR Records'!$O$3:$O$100,"<="&EOMONTH($I4,1))>20
Add to the OR - for status needed -

=IF(OR(COUNTIFS('PR Records'!$I$3:$I$127,B4,'PR Records'!$O$3:$O$127,">="&DATE(YEAR($I4),MONTH($I4),DAY(1)),'PR Records'!$O$3:$O$127,"<="&EOMONTH($I4,1))>20,D4="Do Not Disturb", E4="Out Of Office",COUNTIFS(Table5[Reviewer],'PR Availability'!B4,Table5[Review Status],"Pending")>1,COUNTIFS(Table5[Reviewer],'PR Availability'!B4,Table5[Test Column],TODAY(),Table5[Review Status],"Completed">1)),"Not Available","Available")

but your sample does not have a name for 20 records - so we can see that working with the sample data

i have unhidden columns but tried to add - no idea what is going on in the spreadsheet - and i do NOT run any macros on samples

as i say sorry , i'm not following - hopefully someone will be along and see what you want exactly

OH Ok, Current month - based on todays date
so for my formula
=IF(OR(COUNTIFS('PR Records'!$I$3:$I$127,B4,'PR Records'!$O$3:$O$127,">="&DATE(YEAR(today()),MONTH(today()),DAY(1)),'PR Records'!$O$3:$O$127,"<="&EOMONTH(today(),1))>20,D4="Do Not Disturb", E4="Out Of Office",COUNTIFS(Table5[Reviewer],'PR Availability'!B4,Table5[Review Status],"Pending")>1,COUNTIFS(Table5[Reviewer],'PR Availability'!B4,Table5[Test Column],TODAY(),Table5[Review Status],"Completed">1)),"Not Available","Available")

should work - note if you open the spreadsheet on March 1st or any day upto march the 31st - it will look for march 24 in column O in Pr records

I also wasnt sure of the

where date to be considered is from column O or P (hidden).

which is it O or P or do you need to count BOTH columns
then just add the countifs with P as the criteria columns in the OR

I do not like using full column references - as they are just over 1 million rows

4. If name under column B in sheet "PR Availability" appears under column I in sheet "PR Records" and has status as Completed more than 1 in count under column N (PR Availability) in a single day where the date is considered from column P (hidden)

ok, so a single day in the current month, so today()
although i guess it does not matter

can a helper column go into the PR records sheet
i would add a simple count there

=COUNTIFS($I$3:$I$100,I3,$O$3:$O$100,O3)

as its anyday , then trying to see how you would use duplicate entries for Name and Date in the main formula - to isolate every day in a month


then you can simple add an OR
if helper column > 1

ok
ORGINAL
=IF(
OR(
D4="Do Not Disturb",
E4="Out Of Office",
COUNTIFS(Table5[Reviewer],'PR Availability'!B4,Table5[Review Status],"Pending")>1,
COUNTIFS(Table5[Reviewer],'PR Availability'!B4,Table5[Test Column],TODAY(),Table5[Review Status],"Completed">1)),

"Not Available","Available")


NEW
=IF(
OR(
COUNTIFS('PR Records'!$I$3:$I$127,B4,'PR Records'!$O$3:$O$127,">="&DATE(YEAR(today()),MONTH(today()),DAY(1)),'PR Records'!$O$3:$O$127,"<="&EOMONTH(today(),1))>20,

D4="Do Not Disturb",
E4="Out Of Office",
COUNTIFS(Table5[Reviewer],'PR Availability'!B4,Table5[Review Status],"Pending")>1,
COUNTIFS(Table5[Reviewer],'PR Availability'!B4,Table5[Test Column],TODAY(),Table5[Review Status],"Completed">1)) ,
"Not Available","Available")

Apart from the extra condition added - the OR is the same so should still work for point 4 if it worked before

Its perfect now. Just a change of symbol. My apologies for the trouble.

Formula:

Please Login or Register  to view this content.

Thanks a bunch.

you are welcome, no trouble - i did not see the missing )