Coding a Countifs formula when the Criteria is "=Yes"

rschmidt46

New Member
Joined
Aug 24, 2015
Messages
46
Office Version
  1. 2010
Platform
  1. Windows
My spreadsheet has numerous columns where the value in certain rows is "Yes". I am trying to code a countif formula that counts the number of instances where "Yes" is in two (or occasionally three) columns. For example, there is a Yes in both Column V and Column W in five out of 65 rows. The manually input formula is "=COUNTIFS(V2:V65,"=Yes",W2:W65,"=Yes")" and it works correctly. However, I cannot figure out how to code this formula in VBA. I dimensioned integers "LRow" as the bottom row, "S_Col" for Column V and "A_Col" for Column W. I also dimensioned S_Rng as a string for Range("V2:V65") and A_Rng as a string for Range("W2:W65").

The formula goes in the row below the bottom row (LRow + 1) and column "S_Col". Since the criteria for counting is "=Yes", I cannot get the syntax correct. The double quote is always interpreted incorrectly, even if I use two or three of them. I tried replacing double quotes with CHR(34) but that does not work either. My latest attempt is as follows:

Cells(LRow + 2, Sop_Col).Formula = "=COUNTIFS(" & S_Rng & ",=" & Chr(34) & "Yes," & Chr(34) & "," & A_Rng & "=" & Chr(34) & "Yes" & Chr(34) & ")"

What am I doing wrong? The top row of each criteria range will always be 2 but the bottom row of each criteria range varies so I use LRow to identify the bottom row.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
What happens when you run this code?

I also dimensioned S_Rng as a string for Range("V2:V65") and A_Rng as a string for Range("W2:W65").
Not sure what this means. Do you mean you have this:
VBA Code:
S_Rng = "V2:V65"
A_Rng = "W2:W65"

You mentioned S_Col but it is not in your code. What is Sop_Col?
VBA Code:
Sop_Col = "V"
You mentioned A_Col but I do not see that in your code.

If my assumptions above are correct then this will work:

VBA Code:
Cells(LRow + 2, Sop_Col).Formula = "=COUNTIFS(" & S_Rng & ",""=Yes""," & A_Rng & ",""=Yes"")"
 
Upvote 0
What happens when you run this code?


Not sure what this means. Do you mean you have this:
VBA Code:
S_Rng = "V2:V65"
A_Rng = "W2:W65"

You mentioned S_Col but it is not in your code. What is Sop_Col?
VBA Code:
Sop_Col = "V"
You mentioned A_Col but I do not see that in your code.

If my assumptions above are correct then this will work:

VBA Code:
Cells(LRow + 2, Sop_Col).Formula = "=COUNTIFS(" & S_Rng & ",""=Yes""," & A_Rng & ",""=Yes"")"
 
Upvote 0
Hello Jeff,

Thanks for replying. I apologize for calling "Sop_Col" "S_Col" and "Alt_Col" "A_Col". The applicable column headers are "Soprano" and "Alto". The criteria range "S_Rng" is defined by variables. The column is the one in which the heading is "Soprano" is found in the Find statement. The top row of S_Rng is 2 (always) while the bottom row is LRow as defined by [A500].end(xlup).row. So, the full definition of S_Rng is Cells(2, Sop_Col).Address & ":" & Cells(LRow, Sop_Col).Address. The same method applies to "A_Rng". S_Rng really stands for "Soprano Range" and A_Rng really stands for "Alto Range". Each row is the recond of a person. If the person sings both soprano and alto, the two cells are highlighted. The code does the same thing (except for using different colors) for those who sing 1) Alto and Tenor, 2) Tenor and Baritone, 3) Baritone and Bass or 4) Tenor, Baritone and Bass.

After the highlighting is done I try to insert the COUNTIFS formula to count the number of people who indicated they can sing two or three vocal parts. Everything executes properly until the line where I am trying to code the formula. At that point I get a "Variable not Defined" or something like that. I am struggling with the syntax of the formula. If I could get it right, I'd rather use A1 style than R1C1, because I am much more familiar with it. The sticking point is where the criteria is "=Yes". If the person sings soprano, there's a "Yes" in the column. If she sings alto, there's a "Yes" in that column. I do not generate the spreadsheet. It is run by a third-party on a weekly basis from a church festival registration data base. Since the reports vary in the number of records I built in the flexibility to define the last record as being in the row called "LRow". The columns are the same in each weekly report but I find it easier to work with a macro when it identifies a column as something like "Sop_Col" rather than "V" or "22".

Below is the whole sub, with various attempts at the formula code included but commented out. This includes several lines copied from a recoded macro that I tried to modify.

Sub Highlight_2_Or_More_Voice_Parts()
Const S = "Soprano"
Const A = "Alto"
Const T = "Tenor"
Const B1 = "Baritone"
Const B2 = "Bass"
Dim V As Integer, Str, S_Rng, A_Rng As String
LRow = [B500].End(xlUp).Row
Rows("1:1").Select
Sop_Col = Selection.Find(What:=S, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column
Alt_Col = Selection.Find(What:=A, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column
Ten_Col = Selection.Find(What:=T, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column
B1_Col = Selection.Find(What:=B1, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column
B2_Col = Selection.Find(What:=B2, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column
For V = 2 To LRow
If Cells(V, Sop_Col).Value = "Yes" And Cells(V, Alt_Col).Value = "Yes" Then
Range(Cells(V, Sop_Col), Cells(V, Alt_Col)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
GoTo 10
End If
If Cells(V, Ten_Col).Value = "Yes" And Cells(V, B1_Col).Value = "Yes" And Cells(V, B2_Col).Value = "Yes" Then
Range(Cells(V, Ten_Col), Cells(V, B2_Col)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark2
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
GoTo 10
End If
If Cells(V, Alt_Col).Value = "Yes" And Cells(V, Ten_Col).Value = "Yes" Then
Range(Cells(V, Alt_Col), Cells(V, Ten_Col)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
GoTo 10
End If
If Cells(V, Ten_Col).Value = "Yes" And Cells(V, B1_Col).Value = "Yes" Then
Range(Cells(V, Ten_Col), Cells(V, B1_Col)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
GoTo 10
End If
If Cells(V, B1_Col).Value = "Yes" And Cells(V, B2_Col).Value = "Yes" And Not Cells(V, Ten_Col).Value = "Yes" Then
Range(Cells(V, B1_Col), Cells(V, B2_Col)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
GoTo 10
End If
10:
Next V
S_Rng = Cells(2, Sop_Col).Address & ":" & Cells(LRow, Sop_Col).Address
A_Rng = Cells(2, Alt_Col).Address & ":" & Cells(LRow, Alt_Col).Address
'The following is part of one attempt to rectify the problem:
Str = "="
Str = Str & "Yes"

'Cells(LRow + 2, Sop_Col).Formula = "=COUNTIFS(" & S_Rng & "," = "Yes""," & A_Rng & "," = "Yes"")"
'Cells(LRow + 2, Sop_Col).Formula = "=COUNTIFS(" & S_Rng & "," & Str & "," & A_Rng & "," & Str & ")"
'Cells(LRow + 2, Sop_Col).Formula = "=COUNTIFS(" & S_Rng & ",=" & Chr(34) & "Yes," & Chr(34) & "," & A_Rng & "=" & Chr(34) & "Yes" & Chr(34) & ")"
Cells(LRow + 2, Sop_Col).Select
'ActiveCell.FormulaR1C1 = "=COUNTIFS(R2C:R[-4]C,""=Yes"",R[-69]C[1]:R[-4]C[1],""=Yes"")"
'ActiveCell.FormulaR1C1 = "=COUNTIFS(R2C:R[-6]C,""=Yes"",R[-69]C[1]:R[-6]C[1],""=Yes"")"
ActiveCell.FormulaR1C1 = "=COUNTIFS(R[-65]C:R[-2]C,""Yes"",R[-65]C[1]:R[-2]C[1],""Yes"")"
'ActiveCell.FormulaR1C1 = "=COUNTIFS(R[" & -(LRow) & "]C:R[-2C,""Yes"",R[" & -(LRow) & "C[1]:R[-2]C[1],""Yes"")"
'=COUNTIFS($V$2:$V$65,="Yes",$W$2:$W$65,="Yes")
'cells(LRow,Sop_Col).formula = "=COUNTIFS(" & cells(2,LRow).address & ",""=Yes"" & cells(2,Alt_Col) & ",""=Yes"")"
'=COUNTIFS(V$2:V65,"=Yes",W2:W65,"=Yes")

End Sub

I hope you can identify
 
Upvote 0
I'll wade through all of that if needed but not until you let me know if you tried my code.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top