Countif until blank cell (mutiple blank cells)

I don't think a formula can do this? Way beyond me if it can. Pretty easy in VBA though. Have you used/considered VBA?

Thank you for your kind commnet yay. Yes, I can consider vba code. Can you please suggest a vba code for this?

Cheers
plasma33

Please upload a sample for me to work with and I will be happy to. I don't have any random genome bits on my computer. :P

I have uploaded a short version of my file with 2 sequences...thanks guys!!

In which cells do you want the output?

I put this formula in XFA1:XFD17.

Formula:

=IF(COUNTIF($A1:$XEZ1,"")=COLUMNS($A1:$XEZ1),"",COUNTIF($A1:$XEZ1,INDEX({"A","T","G","C"},,COLUMNS($A:A))))

Sure guys. I can do that but the file size is around 170mb. Hope that's fine.

Cheers
plasma33

Just delete most of the rows. A sample which contains a few sequences will be fine. Upload the whole thing if you want but it's not necessary.

Sub ATGCCount()

Dim DataSht As Worksheet
Set DataSht = ActiveSheet

Dim cntA As Double
Dim cntT As Double
Dim cntG As Double
Dim cntC As Double

Dim CountSheet As Worksheet
Worksheets.Add Before:=Sheets(1)
Set CountSheet = Sheets(1)

CountSheet.[A1:E1] = Array("SEQUENCE NUMBER", "A", "T", "G", "C")

DataSht.Activate

Dim SequenceCount As Long

For i = 1 To ActiveSheet.UsedRange.Rows.Count
    If Application.WorksheetFunction.CountA(Rows(i)) > 0 Then
        For j = 1 To Columns.Count
            Select Case Cells(i, j)
                Case "A": cntA = cntA + 1
                Case "T": cntT = cntT + 1
                Case "G": cntG = cntG + 1
                Case "C": cntC = cntC + 1
            End Select
        Next j
    Else
        SequenceCount = SequenceCount + 1
        CountSheet.Cells(SequenceCount + 1, 1) = SequenceCount
        CountSheet.Cells(SequenceCount + 1, 2) = cntA
        CountSheet.Cells(SequenceCount + 1, 3) = cntT
        CountSheet.Cells(SequenceCount + 1, 4) = cntG
        CountSheet.Cells(SequenceCount + 1, 5) = cntC
        cntA = 0
        cntT = 0
        cntG = 0
        cntC = 0
    End If
Next i

End Sub

This makes a new page at the beginning of the workbook with a summary of the sequences and their counts. It is giving some extra sequences on the sample data because of the deleted info (showing 7 extra sequences with no data) but it should work nicely on your actual data. Paste into a regular module and run from the sheet with the data on it.

Thanks guys for all your input here.
@yay: your vba code worked like a charm. great work. thank u heaps!!
@FlameRetired: your formula worked but its giving me a total ATGC count. i wanted the count for A, T, G and C individually and for each sequence.
@bebo: your formula worked but it will take a long long time as i have around 300+ sequences.

Thanks again, guys.

i am marking this post as solved now.

Cheers
plasma33

You're welcome.