Excel formula to count records with 2 criteria

I've written a very simple function and procedure to accomplish this. You can paste the code into a module in Excel, then choose Tools, Macros, and Run the HowMany procedure (Macro). A message box will pop up with the fotal number of * and g added together for the ranges, If you're not familiar with how to do this, let me know and I'll send you a copy, or post it to my web site for download.

Update - I've placed the sample file for download << here >>

Function CountLetters()
'
' Count number of cell having * in B9:B250 or g in E9:E250
    Static iCount As Integer
    Dim c As Range
    iCount = 0
    For Each c In Range("B9:B250")
        If InStr(c, "*") Then
            iCount = iCount + 1
        End If
    Next
    For Each c In Range("E9:E250")
        If InStr(c, "g") Then
            iCount = iCount + 1
        End If
    Next
    CountLetters = iCount
    MsgBox CountLetters

End Function

Sub HowMany()
    Call CountLetters
End Sub

 

Message Edited by abach on 01-24-2004 10:43 PM

Abach,

I did not interpret the original question in quite the way you did. Your macro gives the total number of times '*' appears in B9 to B250, plus the number of times 'g' appears in E9 to E250.

As I read it the questioner has records in rows 9 to 250 and wants to know how many of those simultaneously has '*' in the B column and  'g' in the E column, which of course is not the same thing.

If that is what was meant, then no doubt you could teach us how to do that instead....:-)

I hope this clarifies rather than confuses!

Jean,

I thought the same at first, but after reading the post again, changed how I approached this. I'll wait until the original poster replies. It is very easy to change.

Thank  you very much for your help with my problem.  I'm sorry I wasn't clearer.  I'm trying to get a cell at the top of my worksheet to keep a running total of those records that have both "x" in column B and "y" in column E.  I assumed it could just be a formula in the cell, rather than a more complicated macro, but whatever will work!  Thank you again.

Would this work:

=SUM(IF(B9="*",IF(E9="g",1,0),0))

It looks at B9 for an * and E9 for a g, if both are true, you'll get a 1. You can then sum the results.

You may just want to enter an array formula in the cell that will list the number of times * and g are true:

{=SUM(IF(B9:B250="*",IF(E9:E250="g",1,0),0))}

To enter an array, type the formula (without the brackets), then press SHIFT+CTRL+ENTER. An array formula is more efficient, and does not require an autofill down 200+ rows.

Message Edited by abach on 01-27-2004 08:36 PM

Are you sure the syntax is correct. It works on my worksheet.

Don't enter the brackets if you're using an array. Also, I'm assuming there is only one character in each cell, either an * in Column B or a g in Column E/

Allan,

Thank you for responding.  I tried it, but it didn't work:  don't think the SUM function is recognizing the IF result as a number.  I also tried

{=SUM(IF(AND(B9:B250="*",E9:E250="g"),1,0))} 

                                                                              which returned 0 (test result should be 3)

{=COUNT(IF(AND(B9:B250="*",E9:E250="g"),1,0))}  which returned 1 (test result should be 3)

Yes, MY mistake,

  =SUM(IF(B9:B250="*",IF(E9:E250="g",1,0),0)) 
  (I previously typed the {}s to show I was entering it as an array)

DOES work, if I use a * in column B. 

I had been using the * as a wildcard for any text there.  Actually, there are checkmarks (square root symbols (221A unicode/hex) in the column), but I didn't know how to enter them.  Is there a way to use a wildcard in this formula?  I can change the check marks to something else. 

This forum is an incredible resource.  Thank you very much for solving my problem and helping me learn something in the process!

Could you give me an example of what is in a cell?

To insert 221A unicode(hex), go to Insert, symbol, symbols tab, in the font drop down box select MS Reference serif font, in the subset dropdown box select Mathemastical oprerators. Third row extreme left.

PS It's also in MS Reference sans serif, looks a bit different; choose whichever you prefer

Message Edited by JRosenfeld on 01-29-2004 02:57 PM