Need Countifs formula for VBA Form control button

[Pat1009]

I have a COUNTIFS formula that works great in the cell, it gets data from a different worksheet (Case Detail),
but I want to use the formula in a Form control button on the first worksheet (client report). This is the formula...

=COUNTIFS('Case Detail'!AR:AR,"bakersfield",'Case Detail'!AN:AN,"US DT/LT MANN M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"bakersfield",'Case Detail'!AN:AN,"US DT/LT MANN HUB 9/0/27")+COUNTIFS('Case Detail'!AR:AR,"bakersfield",'Case Detail'!AN:AN,"US DT/LT DISP MF 8-5 2/0/16")

This is 1 formula of many that I will put into the Form control button, but if I can get help on how to do this one, maybe I can figure out how to add the rest, so I can run the report by clicking 1 button.

I am new to VBA, so any help, I would be grateful.

 

[Grasor]

I believe you might get the functionality you want by taking your code and inserting it into a VBA subroutine then adding the WorksheetFunction object in front of each Countifs. i.e. WorkSheetFunction.Countifs. You may have to change the way you determine you ranges as in VBA you point to ranges differently. Since it looks like everything you are counting is on the same sheet that keeps things simple.

Try the following:

Sub CountMyData()
Dim MyCount As Integer
Dim Response As String

MyCount = WorksheetFunction.CountIfs(Range("AR:AR"), "bakersfield", Range("AN:AN"), "US DT/LT MANN M-F 8-5 2/0/8") + _
            WorksheetFunction.CountIfs(Range("AR:AR"), "bakersfield", Range("AN:AN"), "US DT/LT MANN HUB 9/0/27") + _
            WorksheetFunction.CountIfs(Range("AR:AR"), "bakersfield", Range("AN:AN"), "US DT/LT DISP [COLOR=#ff0000]M-F[/COLOR] 8-5 2/0/16")


Response = MsgBox("The Count is: " & MyCount, vbOKOnly, "Count")

End Sub

Note 1: I highlighted "M-F" in the 3rd set of criteria as I thought this might be a typo as it was given to us as just "MF" in your original post.

Note 2: This will give you a message box with the count but will not paste the data into your sheet anywhere. If you want to paste the data into a sheet then you will need to do something like this at the end of the sub.

Range("A5").Value = MyCount

Note 3: If you are new to VBA then you may need to know that a space followed by an underscore (i.e. " _") at the end of a line of code is your way of telling the compiler that the next line is a continuation of the line above it. Since this was a long string of code I broke it up so it is easier to read.


Let me know if that works for you or if you need any more guidance.
-G

 

[Pat1009]

If I use this code in VBA that is controlled by a Command Button on the Client worksheet, how do I turn off all the formulas in all the cells?
The way I had it setup before was as soon as I updated the "Case Detail" worksheet, all the values in the cells on the Client worksheet changed accordingly.
I now use the button to run the report through VBA. How do I shut off the formulas in the cells ad still use the Command button.

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Total Contracted SLA Calls			203		Total Contracted SLA, T&M, and Out of Scope Calls					428
2	Dedicated Sites 95% Target					Dedicated Sites All Service Request Break Out
3		Total Calls	Missed SLA	Compliance			DT/LT	CTM	SERVER	OO SCOPE	ITC Grand Total					Overall SLA Attainment
4	BAKERSFIELD	3	0	100.00%		BAKERSFIELD	2	7	1	12	22					100.00%
5	BELLAIRE	6	0	100.00%		BELLAIRE/SUGAR LAND	6	2		5	13
6	CONCORD	2	0	100.00%		CONCORD	2	1	0	2	5
7	COVINGTON	9	0	100.00%		COVINGTON	7	5	2	5	19
8	HOU150	101	0	100.00%		HOU150	69	45	32	53	199
9	LAFAYETTE	16	0	100.00%		LAFAYETTE	16	0	0	0	16
10	MIDLAND	8	0	100.00%		MIDLAND	8	3	0	0	11
11	PASCAGOULA	1	0	100.00%		PASCAGOULA	1	4	0	0	5
12	RICHMOND	10	0	100.00%		RICHMOND	9	8	1	10	28
13	SAN RAMON	41	0	100.00%		SAN RAMON	32	22	9	32	95
14
15	Total Dedicated	197	0	100.00%		Total Dedicated	152	97	45	119	413
16
17	Depot Sites 95% Target					Depot Sites All Service Request Break Out
18		Total Calls	Missed SLA	Compliance			DT/LT	CTM	SERVER	OO SCOPE	ITC Grand Total
19	DEPOT San Ramon	4	0	100.00%		DEPOT San Ramon	4	1	0	0	5
20
21	Total Depot	4	0	100.00%		Total Depot	4	1	0	0	5
22
23	Dispatch Sites 85% Target					Dispatch Sites All Service Request Break Out
24		Total Calls	Missed SLA	Compliance			DT/LT	CTM	SERVER	OO SCOPE	ITC Grand Total
25	MOON TOWNSHIP	1	0	100.00%		Moon Township	1	1	0	5	7
26	BREA/EL SEGUNDO	0	0	#DIV/0!		Brea/El Segundo	0	0	0	2	2
27	HONOLULU	1	0	100.00%		HONOLULU	1	0	0	0	1
28
29	Total Dispatch	2	0	100.00%		Total Dispatch	2	1	0	7	10
Client Report

Cell Formulas
Range	Formula
D1	=SUM(B15+B21+B29)
D4	=(B4-C4)/B4
D5	=(B5-C5)/B5
D6	=(B6-C6)/B6
D7	=(B7-C7)/B7
D8	=(B8-C8)/B8
D9	=(B9-C9)/B9
D10	=(B10-C10)/B10
D11	=(B11-C11)/B11
D12	=(B12-C12)/B12
D13	=(B13-C13)/B13
D15	=(B15-C15)/B15
D19	=(B19-C19)/B19
D21	=(B21-C21)/B21
D25	=(B25-C25)/B25
D26	=(B26-C26)/B26
D27	=(B27-C27)/B27
D29	=(B29-C29)/B29
K1	=SUM(K15+K21+K29)
K4	=SUM(G4:J4)
K5	=SUM(G5:J5)
K6	=SUM(G6:J6)
K7	=SUM(G7:J7)
K8	=SUM(G8:J8)
K9	=SUM(G9:J9)
K10	=SUM(G10:J10)
K11	=SUM(G11:J11)
K12	=SUM(G12:J12)
K13	=SUM(G13:J13)
K15	=SUM(K4:K13)
K19	=SUM(G19:J19)
K21	=K19
K25	=SUM(G25:J25)
K26	=SUM(G26:J26)
K27	=SUM(G27:J27)
K29	=SUM(G29:J29)
B4	=SUM(G4,I4)
B5	=SUM(G5,I5)
B6	=SUM(G6,I6)
B7	=SUM(G7,I7)
B8	=SUM(G8,I8)
B9	=SUM(G9,I9)
B10	=SUM(G10,I10)
B11	=SUM(G11,I11)
B12	=SUM(G12,I12)
B13	=SUM(G13,I13)
B15	=SUM(G15)+I15
B19	=SUM(G19,I19)
B21	=SUM(G21,I21)
B25	=SUM(G25,I25)
B26	=SUM(G26,I26)
B27	=SUM(G27,I27)
B29	=SUM(G29,I29)
C4	=COUNTIFS('Missed SLA'!H3:H17,"bakersfield",'Missed SLA'!G3:G17,"missed*")
C5	=COUNTIFS('Missed SLA'!H3:H17,"bellaire",'Missed SLA'!G3:G17,"missed*")
C6	=COUNTIFS('Missed SLA'!H3:H17,"concord",'Missed SLA'!G3:G17,"missed*")
C7	=COUNTIFS('Missed SLA'!H3:H17,"covington",'Missed SLA'!G3:G17,"missed*")
C8	=COUNTIFS('Missed SLA'!H3:H17,"hou150",'Missed SLA'!G3:G17,"missed*")
C9	=COUNTIFS('Missed SLA'!H3:H17,"lafayette",'Missed SLA'!G3:G17,"missed*")
C10	=COUNTIFS('Missed SLA'!H3:H17,"midland",'Missed SLA'!G3:G17,"missed*")
C11	=COUNTIFS('Missed SLA'!H3:H17,"pascagoula",'Missed SLA'!G3:G17,"missed*")
C12	=COUNTIFS('Missed SLA'!H3:H17,"richmond",'Missed SLA'!G3:G17,"missed*")
C13	=COUNTIFS('Missed SLA'!H3:H17,"san ramon",'Missed SLA'!G3:G17,"missed*")
C15	=SUM(C4:C14)
C19	=COUNTIFS('Missed SLA'!H3:H17,"san ramon/dp",'Missed SLA'!G3:G17,"missed*")
C21	=SUM(C19)
C25	=COUNTIFS('Missed SLA'!H3:H17,"moon township",'Missed SLA'!G3:G17,"missed*")
C26	=COUNTIFS('Missed SLA'!H3:H17,"*Segundo",'Missed SLA'!G3:G17,"missed*")
C27	=COUNTIFS('Missed SLA'!H3:H17,"honolulu",'Missed SLA'!G3:G17,"missed*")
C29	=SUM(C25:C28)
J4	=COUNTIFS('Case Detail'!AR:AR,"bakersfield",'Case Detail'!AN:AN,"US OUT OF SCOPE M-F 8-5")
J5	=COUNTIFS('Case Detail'!AR:AR,"bellaire",'Case Detail'!AN:AN,"US OUT OF SCOPE M-F 8-5")
J6	=COUNTIFS('Case Detail'!AR:AR,"concord",'Case Detail'!AN:AN,"US OUT OF SCOPE M-F 8-5")
J7	=COUNTIFS('Case Detail'!AR:AR,"covington",'Case Detail'!AN:AN,"US OUT OF SCOPE M-F 8-5")
J8	=COUNTIFS('Case Detail'!AR:AR,"hou150",'Case Detail'!AN:AN,"US OUT OF SCOPE M-F 8-5")
J9	=COUNTIFS('Case Detail'!AR:AR,"lafayette",'Case Detail'!AN:AN,"US OUT OF SCOPE M-F 8-5")
J10	=COUNTIFS('Case Detail'!AR:AR,"midland",'Case Detail'!AN:AN,"US OUT OF SCOPE M-F 8-5")
J11	=COUNTIFS('Case Detail'!AR:AR,"pascagoula",'Case Detail'!AN:AN,"US OUT OF SCOPE M-F 8-5")
J12	=COUNTIFS('Case Detail'!AR:AR,"richmond",'Case Detail'!AN:AN,"US OUT OF SCOPE M-F 8-5")
J13	=COUNTIFS('Case Detail'!AR:AR,"san ramon",'Case Detail'!AN:AN,"US OUT OF SCOPE M-F 8-5")
J15	=SUM(J4:J13)
J19	=COUNTIFS('Case Detail'!AR:AR,"san ramon/dp",'Case Detail'!AN:AN,"US OUT OF SCOPE M-F 8-5")
J21	=J19
J25	=COUNTIFS('Case Detail'!AR:AR,"moon township",'Case Detail'!AN:AN,"US OUT OF SCOPE M-F 8-5")
J26	=COUNTIFS('Case Detail'!AR:AR,"*Segundo",'Case Detail'!AN:AN,"US OUT OF SCOPE M-F 8-5")
J27	=COUNTIFS('Case Detail'!AR:AR,"honolulu",'Case Detail'!AN:AN,"US OUT OF SCOPE M-F 8-5")
J29	=SUM(J25:J28)
P4	=(D1-C15-C21-C29)/D1
H4	=COUNTIFS('Case Detail'!AR:AR,"bakersfield",'Case Detail'!AN:AN,"US T&M NON TX DISP M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"bakersfield",'Case Detail'!AN:AN,"US T&M NON-TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"bakersfield",'Case Detail'!AN:AN,"US T&M TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"bakersfield",'Case Detail'!AN:AN,"US T&M TX ONLY DISP M-F 8-5")
H5	=COUNTIFS('Case Detail'!AR:AR,"bellaire",'Case Detail'!AN:AN,"US T&M NON TX DISP M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"bellaire",'Case Detail'!AN:AN,"US T&M NON-TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"bellaire",'Case Detail'!AN:AN,"US T&M TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"bellaire",'Case Detail'!AN:AN,"US T&M TX ONLY DISP M-F 8-5")
H6	=COUNTIFS('Case Detail'!AR:AR,"concord",'Case Detail'!AN:AN,"US T&M NON TX DISP M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"concord",'Case Detail'!AN:AN,"US T&M NON-TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"concord",'Case Detail'!AN:AN,"US T&M TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"concord",'Case Detail'!AN:AN,"US T&M TX ONLY DISP M-F 8-5")
H7	=COUNTIFS('Case Detail'!AR:AR,"covington",'Case Detail'!AN:AN,"US T&M NON TX DISP M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"covington",'Case Detail'!AN:AN,"US T&M NON-TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"covington",'Case Detail'!AN:AN,"US T&M TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"covington",'Case Detail'!AN:AN,"US T&M TX ONLY DISP M-F 8-5")
H8	=COUNTIFS('Case Detail'!AR:AR,"hou150",'Case Detail'!AN:AN,"US T&M NON TX DISP M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"hou150",'Case Detail'!AN:AN,"US T&M NON-TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"hou150",'Case Detail'!AN:AN,"US T&M TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"hou150",'Case Detail'!AN:AN,"US T&M TX ONLY DISP M-F 8-5")
H9	=COUNTIFS('Case Detail'!AR:AR,"lafayette",'Case Detail'!AN:AN,"US T&M NON TX DISP M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"lafayette",'Case Detail'!AN:AN,"US T&M NON-TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"lafayette",'Case Detail'!AN:AN,"US T&M TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"lafayette",'Case Detail'!AN:AN,"US T&M TX ONLY DISP M-F 8-5")
H10	=COUNTIFS('Case Detail'!AR:AR,"midland",'Case Detail'!AN:AN,"US T&M NON TX DISP M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"midland",'Case Detail'!AN:AN,"US T&M NON-TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"midland",'Case Detail'!AN:AN,"US T&M TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"midland",'Case Detail'!AN:AN,"US T&M TX ONLY DISP M-F 8-5")
H11	=COUNTIFS('Case Detail'!AR:AR,"pascagoula",'Case Detail'!AN:AN,"US T&M NON TX DISP M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"pascagoula",'Case Detail'!AN:AN,"US T&M NON-TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"pascagoula",'Case Detail'!AN:AN,"US T&M TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"pascagoula",'Case Detail'!AN:AN,"US T&M TX ONLY DISP M-F 8-5")
H12	=COUNTIFS('Case Detail'!AR:AR,"richmond",'Case Detail'!AN:AN,"US T&M NON TX DISP M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"richmond",'Case Detail'!AN:AN,"US T&M NON-TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"richmond",'Case Detail'!AN:AN,"US T&M TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"richmond",'Case Detail'!AN:AN,"US T&M TX ONLY DISP M-F 8-5")
H13	=COUNTIFS('Case Detail'!AR:AR,"san ramon",'Case Detail'!AN:AN,"US T&M NON TX DISP M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"san ramon",'Case Detail'!AN:AN,"US T&M NON-TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"san ramon",'Case Detail'!AN:AN,"US T&M TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"san ramon",'Case Detail'!AN:AN,"US T&M TX ONLY DISP M-F 8-5")
H15	=SUM(H4:H13)
H19	=COUNTIFS('Case Detail'!AR:AR,"San Ramon/Dp",'Case Detail'!AN:AN,"US T&M NON TX DISP M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"San Ramon/Dp",'Case Detail'!AN:AN,"US T&M NON-TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"San Ramon/Dp",'Case Detail'!AN:AN,"US T&M TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"San Ramon/Dp",'Case Detail'!AN:AN,"US T&M TX ONLY DISP M-F 8-5")
H21	=H19
H25	=COUNTIFS('Case Detail'!AR:AR,"moon township",'Case Detail'!AN:AN,"US T&M NON TX DISP M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"moon township",'Case Detail'!AN:AN,"US T&M NON-TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"moon township",'Case Detail'!AN:AN,"US T&M TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"moon township",'Case Detail'!AN:AN,"US T&M TX ONLY DISP M-F 8-5")
H26	=COUNTIFS('Case Detail'!AR:AR,"*Segundo",'Case Detail'!AN:AN,"US T&M NON TX DISP M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"*Segundo",'Case Detail'!AN:AN,"US T&M NON-TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"*Segundo",'Case Detail'!AN:AN,"US T&M TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"*Segundo",'Case Detail'!AN:AN,"US T&M TX ONLY DISP M-F 8-5")
H27	=COUNTIFS('Case Detail'!AR:AR,"honolulu",'Case Detail'!AN:AN,"US T&M NON TX DISP M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"honolulu",'Case Detail'!AN:AN,"US T&M NON-TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"honolulu",'Case Detail'!AN:AN,"US T&M TX M-F 8-5")+COUNTIFS('Case Detail'!AR:AR,"honolulu",'Case Detail'!AN:AN,"US T&M TX ONLY DISP M-F 8-5")
H29	=SUM(H25:H28)
G6	=COUNTIFS('Case Detail'!AR:AR,"concord",'Case Detail'!AN:AN,"US DT/LT MANN M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"concord",'Case Detail'!AN:AN,"US DT/LT MANN HUB 9/0/27")+COUNTIFS('Case Detail'!AR:AR,"concord",'Case Detail'!AN:AN,"US DT/LT DISP MF 8-5 2/0/16")
G7	=COUNTIFS('Case Detail'!AR:AR,"covington",'Case Detail'!AN:AN,"US DT/LT MANN M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"covington",'Case Detail'!AN:AN,"US DT/LT MANN HUB 9/0/27")+COUNTIFS('Case Detail'!AR:AR,"covington",'Case Detail'!AN:AN,"US DT/LT DISP MF 8-5 2/0/16")
G8	=COUNTIFS('Case Detail'!AR:AR,"hou150",'Case Detail'!AN:AN,"US DT/LT MANN M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"hou150",'Case Detail'!AN:AN,"US DT/LT MANN HUB 9/0/27")+COUNTIFS('Case Detail'!AR:AR,"hou150",'Case Detail'!AN:AN,"US DT/LT DISP MF 8-5 2/0/16")
G9	=COUNTIFS('Case Detail'!AR:AR,"lafayette",'Case Detail'!AN:AN,"US DT/LT MANN M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"lafayette",'Case Detail'!AN:AN,"US DT/LT MANN HUB 9/0/27")+COUNTIFS('Case Detail'!AR:AR,"lafayette",'Case Detail'!AN:AN,"US DT/LT DISP MF 8-5 2/0/16")
G10	=COUNTIFS('Case Detail'!AR:AR,"midland",'Case Detail'!AN:AN,"US DT/LT MANN M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"midland",'Case Detail'!AN:AN,"US DT/LT MANN HUB 9/0/27")+COUNTIFS('Case Detail'!AR:AR,"midland",'Case Detail'!AN:AN,"US DT/LT DISP MF 8-5 2/0/16")
G11	=COUNTIFS('Case Detail'!AR:AR,"pascagoula",'Case Detail'!AN:AN,"US DT/LT MANN M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"pascagoula",'Case Detail'!AN:AN,"US DT/LT MANN HUB 9/0/27")+COUNTIFS('Case Detail'!AR:AR,"pascagoula",'Case Detail'!AN:AN,"US DT/LT DISP MF 8-5 2/0/16")
G12	=COUNTIFS('Case Detail'!AR:AR,"richmond",'Case Detail'!AN:AN,"US DT/LT MANN M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"richmond",'Case Detail'!AN:AN,"US DT/LT MANN HUB 9/0/27")+COUNTIFS('Case Detail'!AR:AR,"richmond",'Case Detail'!AN:AN,"US DT/LT DISP MF 8-5 2/0/16")
G13	=COUNTIFS('Case Detail'!AR:AR,"san ramon",'Case Detail'!AN:AN,"US DT/LT MANN M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"san ramon",'Case Detail'!AN:AN,"US DT/LT MANN HUB 9/0/27")+COUNTIFS('Case Detail'!AR:AR,"san ramon",'Case Detail'!AN:AN,"US DT/LT DISP MF 8-5 2/0/16")
G15	=SUM(G4:G13)
G19	=COUNTIFS('Case Detail'!AR:AR,"san ramon/dp",'Case Detail'!AN:AN,"US DT/LT MANN M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"san ramon/dp",'Case Detail'!AN:AN,"US DT/LT MANN HUB 9/0/27")+COUNTIFS('Case Detail'!AR:AR,"san ramon/dp",'Case Detail'!AN:AN,"US DT/LT DISP MF 8-5 2/0/16")+COUNTIFS('Case Detail'!AR:AR,"san ramon/dp",'Case Detail'!AN:AN,"US DEPOT SAN RAMON 2/0/NBD 3C")
G21	=G19
G25	=COUNTIFS('Case Detail'!AR:AR,"moon township",'Case Detail'!AN:AN,"US DT/LT MANN M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"moon township",'Case Detail'!AN:AN,"US DT/LT MANN HUB 9/0/27")+COUNTIFS('Case Detail'!AR:AR,"moon township",'Case Detail'!AN:AN,"US DT/LT DISP MF 8-5 2/0/16")
G26	=COUNTIFS('Case Detail'!AR:AR,"*Segundo",'Case Detail'!AN:AN,"US DT/LT MANN M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"*Segundo",'Case Detail'!AN:AN,"US DT/LT MANN HUB 9/0/27")+COUNTIFS('Case Detail'!AR:AR,"*Segundo",'Case Detail'!AN:AN,"US DT/LT DISP MF 8-5 2/0/16")
G27	=COUNTIFS('Case Detail'!AR:AR,"honolulu",'Case Detail'!AN:AN,"US DT/LT MANN M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"honolulu",'Case Detail'!AN:AN,"US DT/LT MANN HUB 9/0/27")+COUNTIFS('Case Detail'!AR:AR,"honolulu",'Case Detail'!AN:AN,"US DT/LT DISP MF 8-5 2/0/16")
G29	=SUM(G25:G28)
I6	=COUNTIFS('Case Detail'!AR:AR,"concord",'Case Detail'!AN:AN,"US SER DISP 5X9 M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"concord",'Case Detail'!AN:AN,"US SER DISP SITE 7X24 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"concord",'Case Detail'!AN:AN,"US SER MANN 5X9 M-F 8-5 1/0/4")+COUNTIFS('Case Detail'!AR:AR,"concord",'Case Detail'!AN:AN,"US SER MANN SITE 7X24 1/0/4")
I7	=COUNTIFS('Case Detail'!AR:AR,"covington",'Case Detail'!AN:AN,"US SER DISP 5X9 M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"covington",'Case Detail'!AN:AN,"US SER DISP SITE 7X24 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"covington",'Case Detail'!AN:AN,"US SER MANN 5X9 M-F 8-5 1/0/4")+COUNTIFS('Case Detail'!AR:AR,"covington",'Case Detail'!AN:AN,"US SER MANN SITE 7X24 1/0/4")
I8	=COUNTIFS('Case Detail'!AR:AR,"hou150",'Case Detail'!AN:AN,"US SER DISP 5X9 M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"hou150",'Case Detail'!AN:AN,"US SER DISP SITE 7X24 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"hou150",'Case Detail'!AN:AN,"US SER MANN 5X9 M-F 8-5 1/0/4")+COUNTIFS('Case Detail'!AR:AR,"hou150",'Case Detail'!AN:AN,"US SER MANN SITE 7X24 1/0/4")
I9	=COUNTIFS('Case Detail'!AR:AR,"lafayette",'Case Detail'!AN:AN,"US SER DISP 5X9 M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"lafayette",'Case Detail'!AN:AN,"US SER DISP SITE 7X24 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"lafayette",'Case Detail'!AN:AN,"US SER MANN 5X9 M-F 8-5 1/0/4")+COUNTIFS('Case Detail'!AR:AR,"lafayette",'Case Detail'!AN:AN,"US SER MANN SITE 7X24 1/0/4")
I10	=COUNTIFS('Case Detail'!AR:AR,"midland",'Case Detail'!AN:AN,"US SER DISP 5X9 M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"midland",'Case Detail'!AN:AN,"US SER DISP SITE 7X24 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"midland",'Case Detail'!AN:AN,"US SER MANN 5X9 M-F 8-5 1/0/4")+COUNTIFS('Case Detail'!AR:AR,"midland",'Case Detail'!AN:AN,"US SER MANN SITE 7X24 1/0/4")
I11	=COUNTIFS('Case Detail'!AR:AR,"pascagoula",'Case Detail'!AN:AN,"US SER DISP 5X9 M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"pascagoula",'Case Detail'!AN:AN,"US SER DISP SITE 7X24 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"pascagoula",'Case Detail'!AN:AN,"US SER MANN 5X9 M-F 8-5 1/0/4")+COUNTIFS('Case Detail'!AR:AR,"pascagoula",'Case Detail'!AN:AN,"US SER MANN SITE 7X24 1/0/4")
I12	=COUNTIFS('Case Detail'!AR:AR,"richmond",'Case Detail'!AN:AN,"US SER DISP 5X9 M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"richmond",'Case Detail'!AN:AN,"US SER DISP SITE 7X24 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"richmond",'Case Detail'!AN:AN,"US SER MANN 5X9 M-F 8-5 1/0/4")+COUNTIFS('Case Detail'!AR:AR,"richmond",'Case Detail'!AN:AN,"US SER MANN SITE 7X24 1/0/4")
I13	=COUNTIFS('Case Detail'!AR:AR,"san ramon",'Case Detail'!AN:AN,"US SER DISP 5X9 M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"san ramon",'Case Detail'!AN:AN,"US SER DISP SITE 7X24 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"san ramon",'Case Detail'!AN:AN,"US SER MANN 5X9 M-F 8-5 1/0/4")+COUNTIFS('Case Detail'!AR:AR,"san ramon",'Case Detail'!AN:AN,"US SER MANN SITE 7X24 1/0/4")
I15	=SUM(I4:I13)
I19	=COUNTIFS('Case Detail'!AR:AR,"san ramon/Dp",'Case Detail'!AN:AN,"US SER DISP 5X9 M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"san ramon/Dp",'Case Detail'!AN:AN,"US SER DISP SITE 7X24 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"san ramon/Dp",'Case Detail'!AN:AN,"US SER MANN 5X9 M-F 8-5 1/0/4")+COUNTIFS('Case Detail'!AR:AR,"san ramon/Dp",'Case Detail'!AN:AN,"US SER MANN SITE 7X24 1/0/4")
I21	=I19
I25	=COUNTIFS('Case Detail'!AR:AR,"moon township",'Case Detail'!AN:AN,"US SER DISP 5X9 M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"moon township",'Case Detail'!AN:AN,"US SER DISP SITE 7X24 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"moon township",'Case Detail'!AN:AN,"US SER MANN 5X9 M-F 8-5 1/0/4")+COUNTIFS('Case Detail'!AR:AR,"moon township",'Case Detail'!AN:AN,"US SER MANN SITE 7X24 1/0/4")
I26	=COUNTIFS('Case Detail'!AR:AR,"*Segundo",'Case Detail'!AN:AN,"US SER DISP 5X9 M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"*Segundo",'Case Detail'!AN:AN,"US SER DISP SITE 7X24 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"*Segundo",'Case Detail'!AN:AN,"US SER MANN 5X9 M-F 8-5 1/0/4")+COUNTIFS('Case Detail'!AR:AR,"*Segundo",'Case Detail'!AN:AN,"US SER MANN SITE 7X24 1/0/4")
I27	=COUNTIFS('Case Detail'!AR:AR,"honolulu",'Case Detail'!AN:AN,"US SER DISP 5X9 M-F 8-5 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"honolulu",'Case Detail'!AN:AN,"US SER DISP SITE 7X24 2/0/8")+COUNTIFS('Case Detail'!AR:AR,"honolulu",'Case Detail'!AN:AN,"US SER MANN 5X9 M-F 8-5 1/0/4")+COUNTIFS('Case Detail'!AR:AR,"honolulu",'Case Detail'!AN:AN,"US SER MANN SITE 7X24 1/0/4")
I29	=SUM(I25:I28)

 

[Grasor]

Hmm...I need some clarification.

I'm not sure why you would want to turn off any formulas but if that's what you need then you could set your Calculation Options to "Manual" on the Formula Ribbon. Then you would need to click the "Calculate Now" button every time you wanted formulas on the worksheet to recalculate.

Do you mean you want to replace the formulas you are currently using on the sheet with the values calculated by the VBA code?

-G

 

[Pat1009]

Sorry, yes, that is what I mean.

 

[Grasor]

Alright that's an easy one. So instead of formulas in those cells the VBA subroutine will just paste the calculated value into a range you define.

There are several ways of doing this:

In the formula I provided the "Count" that is calculated using the code is saved to a variable called "MyCount"

You can assign the value of "MyCount" to a range on your worksheet. Note: In Excel both a single cell, or a selection of cells, are refered to as a 'range.'

So at the end of the subroutine after the calculation you want is completed you just use the following expression:

 Range("A1") = MyCount

You may change Range("A1") to a named range if you have it defined as well

 Range("MyNamedRange") = MyCount

Named ranges are useful if you may insert rows in the middle of the sheet. That way you won't have to go back to VBA and adjust your ranges when your cells change position. Named ranges also work across sheets in a workbook so you don't have to activate the sheet you want to look for the ranges on.

So for the 'Bakersfield' example you provided; if you wanted the total count to end up in Cell G4 you would do this:

Sub CountMyData()
Dim MyCount As Integer
Dim Response As String
MyCount = WorksheetFunction.CountIfs(Range("AR:AR"), "bakersfield", Range("AN:AN"), "US DT/LT MANN M-F 8-5 2/0/8") + _
            WorksheetFunction.CountIfs(Range("AR:AR"), "bakersfield", Range("AN:AN"), "US DT/LT MANN HUB 9/0/27") + _
            WorksheetFunction.CountIfs(Range("AR:AR"), "bakersfield", Range("AN:AN"), "US DT/LT DISP M-F 8-5 2/0/16")

Range("G4") = MyCount


'Repeat with new criteria<next Countifs="" of="" set=""><next Countifs="" of="" set="">

'Paste new MyCount value to appropriate cell

'etc.<etc><etc>


End Sub

Does that help?</etc></etc></next></next>

 

[Pat1009]

Something wrong with this. Above in the VBA code, where the WorksheetFunction.CountIfs(Range("AR:AR"), "bakersfield", Range("AN:AN"), "US DT/LT MANN M-F 8-5 2/0/8") is, it does not have the worksheet location (it is on a worksheet called Case Detail), the out put of values should go to Cell G4 on a different worksheet (it is called Client Report).

 

[Grasor]

At the top of the code activate the worksheet you need for that part of the code to be used on.

Worksheets("MySheet1").Activate

Where "MySheet1" is your desired sheetname.

You may also name all of your ranges instead.

 

[Pat1009]

Yes, thank you so much Grasor!, I will like it.