PDA

View Full Version : Solved: countif function



sukumar.vb
08-28-2011, 06:02 AM
How to use Excel countif function in VBA?
:bug:

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

:( :help

Bob Phillips
08-28-2011, 06:58 AM
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

sukumar.vb
08-28-2011, 03:42 PM
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? :mkay I am looking to use different. :eek:

sukumar.vb
08-28-2011, 06:04 PM
:hi:

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.



:clap:

Bob Phillips
08-29-2011, 02:47 AM
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

Bob Phillips
08-29-2011, 02:50 AM
And yes, a pivot would work great.

sukumar.vb
08-30-2011, 11:47 AM
You are really distinguished member of VBAX.