AboutAdelaide carvalho Expertise I will be able to answer any question on Excel and Visual Basic for Applications - class modules, recursive procedures and functions are my favourite
Experience
Past/Present clients Several readers of my Books think they are useful.
I have a sheet with data I need to analyse by areas of a city. In column A, I have postcodes (BD1, BD2, etc) and in columns C-E I have codes for types of response (x,y,z, etc); simply put I need a formula to count each occurence of response 'x' for area code BD1 - so that next to the summary for BD1 I can have a cells showing the total of responses for x,y and z respctively, drawn from the raw data.
The problem seems to be that I can't make a COUNTIF function look at both fields in order. Can you see where I'm going wrong?
Thanks
Dean
Answer Ho Dean
How are you?
Excel 2007 has the function Countifs do this
If you are familiar with VBA, please try the following VBA macro:
Sub countingIFSS()
Dim R1 As Range, R2 As Range
Set R1 = Range("A1:A500")
Set R2 = Range("C1:E500")
tx = 0
For Each Cel In R1
If Cel = "Bd1" Then
I = Cel.Row
For J = 1 To R2.Columns.Count
If R2.Cells(I, J) = "x" Then
tx = tx + 1
End If
Next J
End If
Next Cel
MsgBox "no. of x=" + Str(tx)
End Sub
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
