# Need Countifs formula for VBA Form control button

#### Pat1009

##### Active Member
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.

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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:

Code:
``````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

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
ABCDEFGHIJKLMNOP
1Total Contracted SLA Calls203Total Contracted SLA, T&M, and Out of Scope Calls428
2Dedicated Sites 95% TargetDedicated Sites All Service Request Break Out
3Total CallsMissed SLAComplianceDT/LTCTMSERVEROO SCOPEITC Grand TotalOverall SLA Attainment
4BAKERSFIELD30100.00%BAKERSFIELD 2711222100.00%
5BELLAIRE60100.00%BELLAIRE/SUGAR LAND62513
6CONCORD20100.00%CONCORD 21025
7COVINGTON90100.00%COVINGTON 752519
8HOU1501010100.00%HOU15069453253199
9LAFAYETTE160100.00%LAFAYETTE 1600016
10MIDLAND80100.00%MIDLAND830011
11PASCAGOULA10100.00%PASCAGOULA 14005
12RICHMOND100100.00%RICHMOND 9811028
13SAN RAMON410100.00%SAN RAMON322293295
14
15Total Dedicated1970100.00%Total Dedicated1529745119413
16
17Depot Sites 95% TargetDepot Sites All Service Request Break Out
18Total CallsMissed SLAComplianceDT/LTCTMSERVEROO SCOPEITC Grand Total
19DEPOT San Ramon40100.00%DEPOT San Ramon41005
20
21Total Depot40100.00%Total Depot41005
22
23Dispatch Sites 85% TargetDispatch Sites All Service Request Break Out
24Total CallsMissed SLAComplianceDT/LTCTMSERVEROO SCOPEITC Grand Total
25MOON TOWNSHIP10100.00%Moon Township11057
26BREA/EL SEGUNDO00#DIV/0!Brea/El Segundo00022
27HONOLULU10100.00%HONOLULU10001
28
29Total Dispatch20100.00%Total Dispatch210710
Client Report
Cell Formulas
RangeFormula
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)

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

Sorry, yes, that is what I mean.

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:

Code:
`` Range("A1") = MyCount``

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

Code:
`` 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:

Code:
``````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>

Last edited:
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).

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.

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

Replies
4
Views
183
Replies
5
Views
353
Replies
4
Views
135
Replies
5
Views
104
Replies
0
Views
206

1,196,516
Messages
6,015,664
Members
441,914
Latest member
VBAllTheThings

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back