count according to criteria

[ritati]

Help please, don't know if it's easy:

Column:
A B C
1 red
1 red
2 green
3 red
3 red
4 yellow


how can i count red in column B = 2, not 4 cause in column A same numbe

[GarysStudent]

This can be done without VBA, but with a macro:

Sub ritati()
Dim IAmTheCount As Long, N As Long, L As Long
IAmTheCount = 0
N = Cells(Rows.Count, "A").End(xlUp).Row
Dim w As WorksheetFunction, r As Range
Set w = Application.WorksheetFunction
For L = 1 To N
If Cells(L, 2).Value = "red" Then
Set r = Range(Cells(1, 1), Cells(L, 1))
If w.CountIf(r, Cells(L, 1).Value) > 1 Then
Else
IAmTheCount = IAmTheCount + 1
End If
End If
Next
MsgBox IAmTheCount
End Sub

[Bob Phillips]

This array formula will also do it

=SUM(N(FREQUENCY(IF(B1:B100="red",MATCH(A1:A100&"",A1:A100&"",0)),MATCH(A1:A100&"",A1:A100&"",0))>0))

[snb]

=SUMPRODUCT((B1:B6="red")*(COUNTIF(OFFSET(A1;;;ROW(B1:B6));A1:A6)=1))

[ritati]

Thank you all,
my error i know but to clarify:
my request is a general formula or macro:
to count not only specified value (ex: red)
but all values in column B according to the value corresponding in column A,
in my example also to count green, yellow...

[GarysStudent]

Attached is an example.

The original data is in columns A & B
The results are in columns E & F

Columns C & D are "helper" columns.

[ritati]

Thank you