Formula to count values in two columns

[Guest]

Microsoft Excel 2000:
Is it possible to count values from two columns. I know that to count
values from one column the formula is =COUNTIF(B2:B20,"NEW"), but I need to
count a value from a second column also. For example:
Column A Column B
NEW critical
NEW critical
CLOSED major
NEW major

I need a formula that will count the value for "NEW" in Column A that are
"critical" in Column B. The count for this example should be 2. The count
for NEW - major should be 1 and CLOSED - major should be 1. Can anyone help?
THANKS!!

 

[Guest]

hi,

=sumproduct(--(a2:a1000="new")*(b2:B1000="critical"))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"JBurlage" escreveu:

 

[Guest]

You can use the following custom function to get your result:

Press ALT + F11 to open the VBE and then click INSERT on the menu and select
MODULE. You can copy and paste everthing from Function ... down to End
Function.

Using your example and assuming that the first row was row 1, the formula
would be entered =CountNewCrit(A1:B4)

Function CountNewCrit(CellAddress As Range) As Long


Dim r As Range
Dim l As Long
Dim lngRows As Long
Dim intCount As Integer

Set r = CellAddress
lngRows = r.Rows.Count

For l = 1 To lngRows
If Cells(l, 1) = "New" And _
Cells(l, 2) = "Critical" Then
intCount = intCount + 1
End If
Next l

Exit_Count:

Set r = Nothing
CountNewCrit = intCount
Exit Function

End Function

 

[Guest]

Marcelo - I tried it and it does not work. I get a count of 0.

 

[Guest]

hi,

strange, it works here, could you type your formula?

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"JBurlage" escreveu:

 

[Guest]

Insert column C...enter =concatenate(a1,"-",b1), drag down all rows...insert
column d...countif(a1:b4,c1)....run down all rows. Clean up repeats as
needed.

 

[Guest]

Hi Marcelo - I did a copy and paste from this posting and it would not work,
so I typed it in and now it is working!! Thanks so much for your help. I
appreciate the time and effort you took to help me out.
Regards from Wisconsin (USA)