Formula help

[Stef9910]

Hello wonderful people of this forum,

I am working on my last formula for my workbook ( well i hope its my last formula),

Without creating a minisheet is anyone able to see where I am going wrong,

The formula i am working on is
=IF($AD$400="", COUNTIFS($D$15:$D$300, AG13, $H$15:$H$300, "Scheduled"), IF($AD$400<>"", COUNTIFS($AG$15:$AG$300, $BC$3, $D$15:$D$300, AG13, $H$15:$H$300, "Scheduled", $D$15:$D$300, $AD$400), COUNTIFS($D$15:$D$300, AG13, $H$15:$H$300, "Scheduled")))

I would like the cell this formula is in to display the total if there is no selection in AD400.

d15:d300 contains the regions.
h15:h300 contains the type (scheduled or unscheduled)
ag15:ag300 contains conditional formatted cells with either a 1 or 2 in it.
bc3 contains the 1
ag13 refers to the date

i could do a minisheet, but the workbook is massive and would take awhile to redact sensitive information.

If anyone is able to see where or what i need to change in the above formula to make the total display when there is no selection in AD400, that would be amazing.

Thankyou for time and any help will be greatly appreciated.

Thanks Stefan

 

[awoohaw]

@Stef9910 , you don't need to sanitize your entire data. just what needs to be enough for the forum to workwith.
Hide columns not in the calculation. Change Regions and other text to A,B,C,D. Numbers and dates can stay the same.
You only need to copy 4 or 5 rows. So, only change 4 or 5 rows.

 

[etaf]

IF($AD$400=""
then by default its false when it does not =""
so the 2nd IF is not necessary

=IF($AD$400="",
COUNTIFS($D$15:$D$300, AG13, $H$15:$H$300, "Scheduled"),
COUNTIFS($AG$15:$AG$300, $BC$3, $D$15:$D$300, AG13, $H$15:$H$300, "Scheduled", $D$15:$D$300, $AD$400) )

this is the same as the rule for IF($AD$400=""
COUNTIFS($D$15:$D$300, AG13, $H$15:$H$300, "Scheduled")))
so will be met when the sell is blank -

BUT is it blank - you say - SELECTION

display the total if there is no selection in AD400.

just put in a cell
=$AD$400=""
see if you get a TRUE


otherwise as said
A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

 

[Stef9910]

IF($AD$400=""
then by default its false when it does not =""
so the 2nd IF is not necessary

=IF($AD$400="",
COUNTIFS($D$15:$D$300, AG13, $H$15:$H$300, "Scheduled"),
COUNTIFS($AG$15:$AG$300, $BC$3, $D$15:$D$300, AG13, $H$15:$H$300, "Scheduled", $D$15:$D$300, $AD$400) )

this is the same as the rule for IF($AD$400=""
COUNTIFS($D$15:$D$300, AG13, $H$15:$H$300, "Scheduled")))
so will be met when the sell is blank -

BUT is it blank - you say - SELECTION

just put in a cell
=$AD$400=""
see if you get a TRUE


otherwise as said
A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

Thanks for your reply etaf,

I have mocked up a minisheet below,

The expected result in E17 is 2 if there is no selection in E16.
The expected result in E17 is Bunbury is in the selection box E16 is 1.
The expected result in E17 is Metro is in the selection E16 is 1.

On my spreadsheet E16 (the selection box) is controlled from a slicer.

The cell containing a 1 or 2 (on this spreadsheet is F2 containing a 1) is vital for the correct operation of the gantt chart that is in the workbook, 1 refers to completed and 2 refers to Failed.

Hope this helps !!

Thank you again for taking time to look at this.

Stefan

Book2
	A	B	C	D	E	F
2	Resource	Region	Type	Status	1/01/2023	1
3	Bunbury	Regional	Unscheduled	Not Completed	2
4	Metro	Metro	Unscheduled	Completed	1
5	Metro	Metro	Unscheduled	Not Completed	2
6	Broome	Regional	Unscheduled	Completed	1
7	Metro	Metro	Unscheduled	Completed	1
8	Metro	Metro	Scheduled	Not Completed	2
9	Metro	Metro	Scheduled	Not Completed	2
10	Metro	Metro	Scheduled	Not Completed	2
11	Metro	Metro	Scheduled	Not Completed	2
12	Metro	Metro	Scheduled	Not Completed	2
13	Metro	Metro	Scheduled	Completed	1
14	Bunbury	Regional	Scheduled	Completed	1
15	West Kimberley	Regional	Unscheduled	Not Completed	2
16	Metro	Metro	Unscheduled	Not Completed	2
17					0
18				Selection above	Result above
Sheet1

Cell Formulas
Range	Formula
E3:E16	E3	=IF(AND(E$13>=$Q3,E$13<=$S3),1,IF(AND(E$13>=$O3,OR(E$13<=$Q3,E$13<=$U3),ISNUMBER($O3)),2,""))
E17	E17	=IF($D$17="", COUNTIFS($E$3:$E$16, $F$2, $C$3:$C$16, "Scheduled"), COUNTIFS($E$3:$E$16, $F$2, $A$3:$A$16, E2, $C$3:$C$16, "Scheduled", $D$3:$D$16, $D$17))

 

[etaf]

no not really
E3 to E16 formula

=IF(AND(E$13>=$Q3,E$13<=$S3),1,IF(AND(E$13>=$O3,OR(E$13<=$Q3,E$13<=$U3),ISNUMBER($O3)),2,""))

referes to column S, Q, U < O ????

so i just get all zeros in column E

The expected result in E17 is 2 if there is no selection in E16.
No selection means blank ?

The expected result in E17 is Bunbury is in the selection box E16 is 1.

The expected result in E17 is Metro is in the selection E16 is 1.

=IF( E16="", 2, If( E16=1, "Bunbury", If( E16=2,"Metro","")))

i suspect i'm missing something here

EXCEL FORUM.xlsx
	A	B	C	D	E	F	G	H
16	Metro	Metro	Unscheduled	Not Completed		0	1	2
17					2		Bunbury	Metro
Sheet1

Cell Formulas
Range	Formula
E17:H17	E17	=IF( E16="", 2, IF( E16=1, "Bunbury", IF( E16=2,"Metro","")))

 

[Stef9910]

no not really
E3 to E16 formula

=IF(AND(E$13>=$Q3,E$13<=$S3),1,IF(AND(E$13>=$O3,OR(E$13<=$Q3,E$13<=$U3),ISNUMBER($O3)),2,""))

referes to column S, Q, U < O ????

so i just get all zeros in column E

The expected result in E17 is 2 if there is no selection in E16.
No selection means blank ?

The expected result in E17 is Bunbury is in the selection box E16 is 1.

The expected result in E17 is Metro is in the selection E16 is 1.

=IF( E16="", 2, If( E16=1, "Bunbury", If( E16=2,"Metro","")))

i suspect i'm missing something here

EXCEL FORUM.xlsx
	A	B	C	D	E	F	G	H
16	Metro	Metro	Unscheduled	Not Completed		0	1	2
17					2		Bunbury	Metro
Sheet1

Cell Formulas
Range	Formula
E17:H17	E17	=IF( E16="", 2, IF( E16=1, "Bunbury", IF( E16=2,"Metro","")))

Hopefully the below is better,

The formula referring to S,Q and so on is for the conditional formatting of the gantt chart.

The selection box, yes you're right Blank.

The Selection box is controlled by a slicer, so depending on what is selected on the slicer is what displays in the selection box.

The expected result in X65 if Bunbury is in the selection box (U62 in this mini sheet) is 1.

The expected result in X65 if Metro is in the selection box (U62 in this mini sheet) is 1.

minisheet 13.6.23 1.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M					R	S		U		W	X
13																							1
14	Region Resourcing	Region	Type	Status	Received date	Received Time	Start Date	Start Time	End Date	End Time	RTP Date	RTP Time					Days, Hours, Minutes until Started	Total Span		Worked on Span		Span %	T
16	Bunbury	Regional	Unscheduled	Not Completed	22/5/23	11:26					2/6/23	12:58						11 Days 1 h 32 m				0%	2
17	Metro	Metro	Unscheduled	Completed	23/5/23	6:30	25/5/23	15:15	7/6/23	14:30							2 days 8 h 45 m	15 Days 8 h 0 m		12 Days 23 h 15 m		85%	1
20	Metro	Metro	Unscheduled	Not Completed	28/5/23	8:38					4/6/23	17:50						7 Days 9 h 12 m				0%	2
21	Broome	Regional	Unscheduled	Completed	29/5/23	15:04	30/5/23	14:05	6/6/23	5:38							0 days 23 h 01 m	7 Days 14 h 34 m		6 Days 15 h 33 m		87%	1
22	Metro	Metro	Unscheduled	Completed	31/5/23	18:02	31/5/23	19:45	1/6/23	7:31							0 days 1 h 43 m	0 Days 13 h 29 m		0 Days 11 h 46 m		87%	1
23	Metro	Metro	Scheduled	Not Completed	1/6/23						1/6/23							0 Days 0 h 0 m				0%	2
24	Metro	Metro	Scheduled	Not Completed	1/6/23						1/6/23							0 Days 0 h 0 m				0%	2
25	Metro	Metro	Scheduled	Not Completed	1/6/23						1/6/23							0 Days 0 h 0 m				0%	2
26	Metro	Metro	Scheduled	Not Completed	1/6/23						1/6/23							0 Days 0 h 0 m				0%	2
27	Metro	Metro	Scheduled	Not Completed	1/6/23						1/6/23							0 Days 0 h 0 m				0%	2
28	Metro	Metro	Scheduled	Completed			1/6/23	10:15	1/6/23	17:22										0 Days 7 h 7 m		100%	1
29	Bunbury	Regional	Scheduled	Completed			1/6/23	5:45	1/6/23	11:20										0 Days 5 h 35 m		100%	1
30	West Kimberley	Regional	Unscheduled	Not Completed	1/6/23	12:21					4/6/23	11:23						2 Days 23 h 2 m				0%	2
31	Metro	Metro	Unscheduled	Not Completed	1/6/23	13:43					2/6/23	18:16						1 Days 4 h 33 m				0%	2
32	Metro	Metro	Scheduled	Completed			2/6/23	8:38	2/6/23	20:15										0 Days 11 h 37 m		100%
33	Metro	Metro	Unscheduled	Not Completed	3/6/23	8:10					3/6/23	15:21						0 Days 7 h 11 m				0%
34	Metro	Metro	Scheduled	Completed			2/6/23	10:16	2/6/23	17:05										0 Days 6 h 49 m		100%
35	Metro	Metro	Scheduled	Completed			2/6/23	10:21	2/6/23	12:47										0 Days 2 h 26 m		100%
36	Metro	Metro	Unscheduled	Completed	1/6/23	17:23	1/6/23	18:26	3/6/23	19:22							0 days 1 h 03 m	2 Days 1 h 59 m		2 Days 0 h 56 m		98%	1
37	Metro	Metro	Unscheduled	Not Completed	2/6/23	0:25					4/6/23	13:26						2 Days 13 h 1 m				0%
38	Metro	Metro	Unscheduled	Not Completed	3/6/23	7:03					4/6/23	23:00						1 Days 15 h 57 m				0%
39	Metro	Metro	Unscheduled	Completed	3/6/23	19:15	3/6/23	21:10	6/6/23	19:19							0 days 1 h 55 m	3 Days 0 h 4 m		2 Days 22 h 9 m		97%
40	Metro	Metro	Unscheduled	Not Completed	3/6/23	7:03					4/6/23	18:13						1 Days 11 h 10 m				0%
41	West Kimberley	Regional	Unscheduled	Completed	30/5/23	14:16	4/6/23	5:30	4/6/23	10:30							4 days 15 h 14 m	4 Days 20 h 14 m		0 Days 5 h 0 m		4%	2
44	Metro	Metro	Scheduled	Completed			7/6/23	10:29	7/6/23	19:15										0 Days 8 h 46 m		100%
45	Metro	Metro	Scheduled	Completed			7/6/23	11:00	7/6/23	17:04										0 Days 6 h 4 m		100%
47	Metro	Metro	Scheduled	Not Completed	7/6/23	7:30					8/6/23							0 Days 16 h 30 m				0%
48	Metro	Metro	Scheduled	Not Completed	8/6/23						8/6/23							0 Days 0 h 0 m				0%
50	Metro	Metro	Scheduled	Not Completed	8/6/23						8/6/23							0 Days 0 h 0 m				0%
51	Metro	Metro	Scheduled	Completed			8/6/23	7:04	9/6/23	8:24										1 Days 1 h 20 m		100%
53	Metro	Metro	Scheduled	Not Completed	9/6/23						9/6/23							0 Days 0 h 0 m				0%
54	Metro	Metro	Scheduled	Not Completed	9/6/23						9/6/23							0 Days 0 h 0 m				0%
57	Metro	Metro	Unscheduled	Not Completed	7/6/23	10:48					7/6/23	21:43						0 Days 10 h 55 m				0%
59	EGRP	Regional	Unscheduled	Not Completed	7/6/23	15:38					8/6/23	16:36						1 Days 0 h 58 m				0%
60	Metro	Metro	Unscheduled	Not Completed	7/6/23	19:15					8/6/23	15:18						0 Days 20 h 3 m				0%
62																		Broome				Completed	6
63																		Bunbury				Failed	10
64																		EGRP
65																		Metro				Scheduled	0
66																		West Kimberley				Unscheduled
67																		(blank)
Worked on Gantt Chart

Cell Formulas
Range	Formula
R16:R17,R20:R41,R44:R45,R47:R48,R50:R51,R53:R54,R57,R59:R60	R16	=IFERROR(IF(O16-N16=DATE(1900,1,0),"",O16-N16),"")
S16:S17,S20:S41,S44:S45,S47:S48,S50:S51,S53:S54,S57,S59:S60	S16	=IFERROR(IF(N16="", "", INT(IF(P16="", Q16, P16)-N16) & " Days " & HOUR(IF(P16="", Q16, P16)-N16) & " h " & MINUTE(IF(P16="", Q16, P16)-N16) & " m"), "")
W16:W17,W20:W41,W44:W45,W47:W48,W50:W51,W53:W54,W57,W59:W60	W16	=IF(AND(T16="", V16=""), 0, IF(T16="", 1, IFERROR(V16/T16, 0)))
U16:U17,U20:U41,U44:U45,U47:U48,U50:U51,U53:U54,U57,U59:U60	U16	=IF(OR(P16="", O16=""), "", INT(P16-O16) & " Days " & HOUR(P16-O16) & " h " & MINUTE(P16-O16) & " m")
U62	U62	=IF(COUNTA(S62:S79)>1,"",IF(S62<>"",S62,""))
X13	X13	=W11
X14	X14	=LEFT(TEXT(X13,"ddd"),1)
X16:X17,X20:X41,X44:X45,X47:X48,X50:X51,X53:X54,X57,X59:X60	X16	=IF(AND(X$13>=$H16,X$13<=$J16),1,IF(AND(X$13>=$F16,OR(X$13<=$H16,X$13<=$L16),ISNUMBER($F16)),2,""))
X62	X62	=IF($U$62<>"", COUNTIFS(X15:X61, $AT$3, $B$15:$B$61, $U62), COUNTIF(X15:X61, 1))
X63	X63	=IF($U$62<>"", COUNTIFS(X15:X61, $AT$5, $B$15:$B$61, $U62), COUNTIF(X15:X61, 2))
X65	X65	=IF($U$62="", COUNTIFS($X$15:$X$61, $AT$3, $B$15:$B$61, X13, $D$15:$D$61, "Scheduled"), COUNTIFS($X$15:$X$61, $AT$3, $B$15:$B$61, $U$62, $D$15:$D$61, "Scheduled"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
X15:BB61	Expression	=AND(X$13>=$H15,X$13<=$J15)	text	NO
X15:BB61	Expression	=AND(X$13>=$F15,OR(X$13<=$H15,X$13<=$L15),ISNUMBER($F15))	text	NO
W15:W61	Other Type	DataBar		NO

Cells with Data Validation
Cell	Allow	Criteria
B15:B61	List	=$ES$35:$ES$46
C15:C61	List	=$ES$15:$ES$17
D15:D61	List	=$ET$15:$ET$17
E15:E61	List	=$EW$15:$EW$21

 

[etaf]

i'm really sorry, been through a few times again, I'm just not understanding what you want , as it seems to be changing each time

The original post - talked about totals

If anyone is able to see where or what i need to change in the above formula to make the total display when there is no selection in AD400, that would be amazing.

And i correct the formula and suggested a mini sheet - BUT that seems to have changed the question

you said in post #4 -

The expected result in E17 is 2 if there is no selection in E16.
The expected result in E17 is Bunbury is in the selection box E16 is 1.
The expected result in E17 is Metro is in the selection E16 is 1.

I gave you a formula
=IF( E16="", 2, If( E16=1, "Bunbury", If( E16=2,"Metro","")))

So that was for a cell E17

Which you have NOT commented - on and in your reply
post #6

The expected result in X65 if Bunbury is in the selection box (U62 in this mini sheet) is 1.

The expected result in X65 if Metro is in the selection box (U62 in this mini sheet) is 1.

Now we are talking about a different cell X65 and a different set of results

before it was to return a word based on a value

Now its a value based on a word

so here goes
which is the same result of 1
=IF( OR( U62="Banbury", U62="Metro") , 1 , "")

as i say , i'm just not following now at all what you need

 

[Stef9910]

i'm really sorry, been through a few times again, I'm just not understanding what you want , as it seems to be changing each time

The original post - talked about totals

And i correct the formula and suggested a mini sheet - BUT that seems to have changed the question

you said in post #4 -

I gave you a formula
=IF( E16="", 2, If( E16=1, "Bunbury", If( E16=2,"Metro","")))

So that was for a cell E17

Which you have NOT commented - on and in your reply
post #6

Now we are talking about a different cell X65 and a different set of results

before it was to return a word based on a value

Now its a value based on a word

so here goes
which is the same result of 1
=IF( OR( U62="Banbury", U62="Metro") , 1 , "")

as i say , i'm just not following now at all what you need

Hi etaf,

Really sorry for being inconsistent, the last minisheet i posted was from my actual spreadsheet with all sensitive data hidden as suggested by awoohaw, thank you awoohaw,

By providing the actual sheet, i thought it would make things clearer, sorry it has not.

Your formula, =IF( E16="", 2, If( E16=1, "Bunbury", If( E16=2,"Metro",""))) is great if the only two selections were Bunbury and Metro, there is more than two and as i said the selection is controlled by a slicer.

From the last minisheet posted, I would like X65 to display the total for scheduled from the 01/06 in cell X13 if there is no selection in U62 (U62 is controlled from a slicer for the pivot table S62 to S67).

So the expected result if no selection is U62 would be 2.
If Bunbury was selected the expected result would be 1.
If Metro was selected the expected result would be 1.

If this does not make sense, thats ok, I confuse myself!!

I am thinking about ditching the idea as it is becoming a headache, but i would like it to work.

I really appreciate your time etaf and I am sorry for the confusion.

 

[etaf]

Your formula, =IF( E16="", 2, If( E16=1, "Bunbury", If( E16=2,"Metro",""))) is great if the only two selections were Bunbury and Metro, there is more than two and as i said the selection is controlled by a slicer.

so what are all the possible selections and result required - a simple lookup on a reference table would solve that quite easily

Broome
Bunbury
EGRP
Metro
West Kimberley
(blank)

So the expected result if no selection is U62 would be 2.
If Bunbury was selected the expected result would be 1.
If Metro was selected the expected result would be 1.

again you have only shown 3 conditions
No Selection , Bunbury & Metro
But said

is great if the only two selections were Bunbury and Metro, there is more than two and as i said the selection is controlled by a slicer.

I am thinking about ditching the idea as it is becoming a headache, but i would like it to work.

sorry its a headache for you and I'm sure an easy solution is available

perhaps a share version of the spreadsheet - with expected results shown ..........

Hopefully another member will pick up the thread and understand what you are after and help further

 

[Stef9910]

so what are all the possible selections and result required - a simple lookup on a reference table would solve that quite easily

Broome
Bunbury
EGRP
Metro
West Kimberley
(blank)



again you have only shown 3 conditions
No Selection , Bunbury & Metro
But said




sorry its a headache for you and I'm sure an easy solution is available

perhaps a share version of the spreadsheet - with expected results shown ..........

Hopefully another member will pick up the thread and understand what you are after and help further

I have taken a different approach to my problem and using a pivot chart in a tabular format using a different criteria from the spreadsheet has solved my problem, it's not exactly as i would have liked it but it works well and displays all the information i need to be displayed.

My brain turned to mush trying to work out the formula.

I thank you so much for your time etaf.