Help! Urgent Excel help needed

Amberdarcy

Hello

I have a spreadsheet which notes 'yes' in the cells. Is there a way of adding how many cells this appears in across a row?

I'm sure this doesn't make sense!

nickmack

Amberdarcy wrote: »

I have a spreadsheet which notes 'yes' in the cells. Is there a way of adding how many cells this appears in across a row?

=COUNTIF(A1:C1,"Yes")

where A1:C1 is your row range.

Wombat

Use DCOUNTA?
http://sysdev.uncc.edu//HowTos/Excel/Using%20DCOUNTA%20to%20Find%20Records%20that%20match%20criteria.htm

Amberdarcy

Thank you so much! i'm impressed.

Amberdarcy

Is there a way of counting how many cells in a word table contain text?

Wombat

Do you mean a table in Microsoft Word, or a table containing words in Excel?

As a start, there's this about extracting data from Microsoft Word tables:

http://msdn2.microsoft.com/en-us/library/aa537149(office.11).aspx#officewordautomatingtablesdata_extractingdatafromatable

Or this referring to counting cells in Excel:

http://j-walk.com/ss/excel/tips/tip52.htm

rmg1

Assuming your data is in row 1 starting in column A, you could do:-

=istext(A1) in row 2 for each cell you want to check, and then at the end of either row 1 or 2, count the number of TRUE's

Chippy_Minton

Amberdarcy wrote: »

Is there a way of counting how many cells in a word table contain text?

Open your Word document (or a copy of it if you want to play around), press Alt+F11, click Insert - Module, and paste in this code:

Sub CountCellsWithText()

    Dim oDoc As Word.Document
    Dim oTable As Word.Table
    Dim oCell As Word.Cell
    Dim r, c, numCells As Integer
        
    numCells = 0
    Set oDoc = ActiveDocument
    Set oTable = oDoc.Tables(1)
    For r = 1 To oTable.Rows.Count
        For c = 1 To oTable.Columns.Count
            Set oCell = oTable.Rows(r).Cells(c)
            If isText(oCell.Range.Text) Then
                numCells = numCells + 1
            End If
        Next
    Next

    MsgBox CStr(numCells) + " cells contain text"
End Sub

Private Function isText(sText As String) As Boolean
    Dim i As Integer
    isText = False
    i = 1
    While i <= Len(sText) And Not isText
        If Mid(sText, i, 1) > " " Then
            isText = True
        Else
            i = i + 1
        End If
    Wend
End Function

Press F5 to run. NB the code looks at the first table in the document.