Countifs to a criteria range that is a derived value

[kattay]

Hi, I have a roster made from the Excel calendar template. The dates have been pre-populated by formula (see E3). I would like to count the number of "Y" in the calendar (eg. row 4 & row 7), until the effective date, but the countifs does not work on the derived value in the criteria range. My formula is COUNTIFS(B4:H4,"Y",B3:H3,"<"&L2)+COUNTIFS(B7:H7,"Y",B6:H6,"<"&L2).

Pls help. Thanks

 

[RachelN76]

What is happening in E4 to make that a Y - is it just typed in by the individual as a value, or is it a formula itself?

 

[kattay]

Hi, it's typed in by individual.

 

[RachelN76]

I get it to work with the numbers entered manually, but it seems to not like them being the result of a formula. I'm afraid I don't know why. I hope someone much wiser than me will come along soon, but I will keep fiddling with it in the meantime to see if what I can do.

 

[awoohaw]

@RachelN76 , you can use SUMPRODUCT function to acheive this. But, can you post data in a table, and not images? It is time consuming and error prone to make the forum recreate your scenario.

 

[kattay]

Hi @awoohaw, it's my table. Pls see below. Thanks

Calendar sample.xlsx
	B	C	D	E	F	G	H	I	J	K	L
1	June 2023										Day
2	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY	SUNDAY		Effective Date of Change:	8/6/2023	8
3	29	30	31	1	2	3	4
4				Y		Y	Y		Old Shift Pattern:	(Select One)
5				Y		Y	Y		New Shift Pattern:	(Select One)
6	5	6	7	8	9	10	11
7	Y	Y	Y			Y			No. of working days (until effective date of change):	0
8	Y	Y	Y			Y			No. of working days (from effective date of change):	0
9	12	13	14	15	16	17	18
10	Y	Y	Y			Y			No. of working days in the month (assume full month roster):	17
11	Y	Y				Y			No. of working days in the month (assume full month roster):	16
12	19	20	21	22	23	24	25
13			Y	Y			Y
14			Y	Y		Y
15	26	27	28	29	30	1	2
16			Y	Y	Y
17		Y		Y	Y
June

Cell Formulas
Range	Formula
B1	B1	="June "&CalendarYear
B2	B2	=IF(WeekStart="SUNDAY", "SUNDAY","MONDAY")
C2:H2	C2	=UPPER(TEXT(C3,"dddd"))
B3:H3	B3	=DaysAndWeeks+DATE(CalendarYear,6,1)-WEEKDAY(DATE(CalendarYear,6,1),(WeekStart="Monday")+1)+1
L2	L2	=DAY(K2)
B6:H6	B6	=DaysAndWeeks+DATE(CalendarYear,6,1)-WEEKDAY(DATE(CalendarYear,6,1),(WeekStart="Monday")+1)+8
K7	K7	=COUNTIFS(B4:H4,"Y",B3:H3,"<"&L2)++COUNTIFS(B7:H7,"Y",B6:H6,"<"&L2)+COUNTIFS(B10:H10,"Y",B9:H9,"<"&L2)+COUNTIFS(B13:H13,"Y",B12:H12,"<"&L2)+COUNTIFS(B16:H16,"Y",B15:H15,"<"&L2)
K8	K8	=COUNTIFS(B5:H5,"Y",B3:H3,"<"&L3)++COUNTIFS(B8:H8,"Y",B6:H6,"<"&L3)+COUNTIFS(B11:H11,"Y",B9:H9,"<"&L3)+COUNTIFS(B14:H14,"Y",B12:H12,"<"&L3)+COUNTIFS(B17:H17,"Y",B15:H15,"<"&L3)
B9:H9	B9	=DaysAndWeeks+DATE(CalendarYear,6,1)-WEEKDAY(DATE(CalendarYear,6,1),(WeekStart="Monday")+1)+15
K10	K10	=COUNTIFS(B7:H7,"<>")+COUNTIFS(B10:H10,"<>")+COUNTIFS(B13:H13,"<>")+COUNTIFS(B16:H16,"<>")+COUNTIFS(B4:H4,"<>")
K11	K11	=COUNTIF(B8:H8,"<>")+COUNTIF(B11:H11,"<>")+COUNTIF(B14:H14,"<>")+COUNTIF(B17:H17,"<>")+COUNTIF(B5:H5,"<>")
B12:H12	B12	=DaysAndWeeks+DATE(CalendarYear,6,1)-WEEKDAY(DATE(CalendarYear,6,1),(WeekStart="Monday")+1)+22
B15:H15	B15	=DaysAndWeeks+DATE(CalendarYear,6,1)-WEEKDAY(DATE(CalendarYear,6,1),(WeekStart="Monday")+1)+29
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:G3	Expression	=DAY(B3)>8	text	NO
B15:H15	Expression	=AND(DAY(B15)>=1,DAY(B15)<=15)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B3	Any value
K4:K5	List	(Select One),Regular hours,2-rotating,3-rotating,Permanent night
B5	Any value
E17	Any value
B1	Any value
C1	Any value
B2	Any value
C2:H2	Any value

 

[awoohaw]

thanks for the data. I think you have some named ranges or maybe lambda functions that are messing up pasting your mini worksheet.
If you can help with "CalendarYear", "Weekstart", and "DaysAndWeeks" it may help. I'll work with pasting values and see how that may get to your solution.
But, if you can get where those values are and the actual values that are in the scenario above it would be helpful if I get stuck.

 

[awoohaw]

Okay, below is my stab at your question.
But, I had to make a few guesses and reconstructions of what I thought you want.

1) I redid the formula for calculating the calendar. Please look at the formula in cell B3.
2) I could not figure out what you meant by DaysAndWeeks, so I ignored that and used my guess at the formula in point 1.
3) Your calculations in cells L8 and L9 had a "double +", and you also reference cell L3 which is blank. You also have the same sign as the comparison operator in both formulas. So, I pretty much only used these formulas to get an idea of what you want. If my guess is wrong, please specify what you need.
4) I ignored your "day of month for consideration" (I'm guessing that is was L2 and L3 are for) in your calculations for L8 and L9. I just used the pure date value in cell K2.
5) The calculation I used in cells M7 and M8 are array formulas and work as a count if function. If you need to insert a calculation for the comparative values you can do that..... Just make sure you wrap that part in parentheses.
6) You can use the same kind of logic for calculations for K10 and K11. I just copied yours since they seemed to work.
7) The new formulas I've provided are K7,K8,K10,K11,B3,B6,B9,B12,B15
8) I do not have any idea what you want to do with the "drop down cells" in Cells K4 and K5.
9) I do no have any idea what you want to do with the "Y" values in rows: 8, 11,14,17
10) Not sure why you are "hard coding" the month name in you could make this formulaic pretty easily.

So, here is my xl2bb mini of your data:
I hope this helps:

Mr excel questions 39.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		June 2023										Day	CalendarYear	WeekStart	DaysAndWeeks
2		MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY	SUNDAY		Effective Date of Change:	2023-06-08	8	2023	MONDAY
3		29	30	31	1	2	3	4
4					Y		Y	Y		Old Shift Pattern:	(Select One)
5					Y		Y	Y		New Shift Pattern:	(Select One)
6		5	6	7	8	9	10	11
7		Y	Y	Y			Y			No. of working days (until effective date of change):			6
8		Y	Y	Y			Y			No. of working days (from effective date of change):			11
9		12	13	14	15	16	17	18
10		Y	Y	Y			Y			No. of working days in the month (assume full month roster):	17		17
11		Y	Y				Y			No. of working days in the month (assume full month roster):	16		16
12		19	20	21	22	23	24	25
13				Y	Y			Y
14				Y	Y		Y
15		26	27	28	29	30	1	2		2023-05-29
16				Y	Y	Y
17			Y		Y	Y
kattay

Cell Formulas
Range	Formula
B1	B1	="June " & CalendarYear
B2:H2	B2	=UPPER(TEXT(B3,"dddd"))
B3	B3	=WORKDAY.INTL(DATE(2023,6,1)+1,-1,IF(WeekStart="MONDAY","0111111","1111110"))
C3:H3,C15:H15,C12:H12,C9:H9,C6:H6	C3	=B3+1
B6,B15,B12,B9	B6	=B3+7
M7	M7	=SUM((--(B4:H4="Y"))*(--(B3:H3)<K2))+ SUM((--(B7:H7="Y"))*(--(B6:H6)<K2))+ SUM((--(B10:H10="Y"))*(--(B9:H9)<K2))+ SUM((--(B13:H13="Y"))*(--(B12:H12)<K2))+ SUM((--(B16:H16="Y"))*(--(B15:H15)<K2))
M8	M8	=SUM((--(C4:I4="Y"))*(--(C3:I3)>=K2))+ SUM((--(C7:I7="Y"))*(--(C6:I6)>=K2))+ SUM((--(B10:H10="Y"))*(--(B9:H9)>=K2))+ SUM((--(B13:H13="Y"))*(--(B12:H12)>=K2))+ SUM((--(C16:I16="Y"))*(--(C15:I15)>=K2))
M10	M10	= COUNTIFS(B7:H7,"<>")+ COUNTIFS(B10:H10,"<>")+ COUNTIFS(B13:H13,"<>")+ COUNTIFS(B16:H16,"<>")+ COUNTIFS(B4:H4,"<>")
M11	M11	= COUNTIF(B8:H8,"<>")+ COUNTIF(B11:H11,"<>")+ COUNTIF(B14:H14,"<>")+ COUNTIF(B17:H17,"<>")+ COUNTIF(B5:H5,"<>")
J15	J15	=WORKDAY.INTL(DATE(2023,6,1)+1,-1,"0111111")

Named Ranges
Name	Refers To	Cells
CalendarYear	=kattay!$M$2	B1
WeekStart	=kattay!$N$2	B3

Cells with Data Validation
Cell	Allow	Criteria
K4:K5	List	,Regular hours,2-rotating,3-rotating,Permanent night