Solved: countif function

[sukumar.vb]

How to use Excel countif function in VBA?


Not as an Excel Formula... so that User Defined Function does not appear on Worksheet, but posts only calculated values on Worksheet.

Refer attached file, where I want VBA to do calculations on Sheet1

[Bob Phillips]

[vba]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i, j

With Worksheets("Sheet1")

For j = 3 To .Range("B1").End(xlToRight).Column

For i = 2 To .Range("A2").End(xlDown).Row

.Cells(i, j).Formula = "=SUMPRODUCT(--(Sheet2!A2:A200=" & .Cells(i, "A").Address & "),--(Sheet2!B2:B200=" & .Cells(1, j).Address & "))"
.Cells(i, j).Value = .Cells(i, j).Value
Next i
Next j
End With
End Sub
[/vba]

[sukumar.vb]

Requesting you to review this code again and ...Please advice SOP.
It might occur when the selection changes on a worksheet.

Can it not happen with Workbook Open event?

Please review attached file here and help me know how to put counts in yellow and Green cells of Sheet1.

Would Pivot table be an idea? I am looking to use different.

[sukumar.vb]

The was Dude's program, I have ever seen. Excellent.
I made some changes in below attachment.
This attachment works, but it needs another "FOR NEXT loop" to calculate Total for each row and col.

I also read your SOP about SUMPRODUCT. G8 Job done.
Thus, awaiting your response to close this thread in your praise.

[Bob Phillips]

[vba]

Private Sub Workbook_Open()
Dim i As Long, j As Long

Application.ScreenUpdating = False

With Worksheets("Sheet1")

.Activate

For j = 2 To .Range("B1").End(xlToRight).Column - 1

For i = 2 To .Range("A2").End(xlDown).Row - 1

.Cells(i, j).Formula = "=SUMPRODUCT(--(Sheet2!A2:A200=" & .Cells(i, "A").Address & "),--(Sheet2!B2:B200=" & .Cells(1, j).Address & "))"
.Cells(i, j).Value = .Cells(i, j).Value
Next i

Next j
.Cells(2, .Range("B1").End(xlToRight).Column).Resize(.Range("A2").End(xlDown).Row - 2).FormulaR1C1 = "=SUM(RC2:RC[-1])"
.Cells(.Range("A2").End(xlDown).Row, 2).Resize(, .Range("B1").End(xlToRight).Column - 1).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
End With

Application.ScreenUpdating = True
End Sub
[/vba]

[Bob Phillips]

And yes, a pivot would work great.

[sukumar.vb]

You are really distinguished member of VBAX.