+ Reply to Thread
Results 1 to 13 of 13

Countif until blank cell (mutiple blank cells)

  1. 05-06-2015, 09:10 PM #1
    plasma33
    plasma33 is offline
    Forum Contributor
    Join Date
    06-20-2011
    Location
    Auckland, NZ
    MS-Off Ver
    Excel 2013
    Posts
    165

    Countif until blank cell (mutiple blank cells)

    Hi guys,

    I am counting the occurrences of 'A', 'T', 'G' and 'C' in a DNA sequence. I have got around 300 large sequences. Each sequence takes multiple lines in excel (due to large sequence). For instance, sequence 1 takes 3 lines, sequence 2 takes 8 lines. So, I need a formula that can count occurrences of 'A', 'T', 'G' and 'C' specifically and separately for 300 sequences in one go and in a sequence by sequence manner (for instance, count occurrences of 'A' in sequence 1, then in sequence 2, and same rule applies for 'T', 'G', and 'C'). Each sequence is separated by a blank row. But the problem is there are some gaps (blank cells) in a sequence (not to be confused by an empty row after each sequence). I am attaching an image for your reference. Please find the attachment. Sorry guys, the image is bit untidy.
    I am currently using this basic formula:
    =countif(A5:XEZ18,"A") =countif(A5:XEZ18,"T") =countif(A5:XEZ18,"G") =countif(A5:XEZ18,"C")

    Thanks guys!!

    Cheers
    plasma33
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by plasma33; 05-06-2015 at 09:49 PM.
    Reply With QuoteRegister To Reply
  2. 05-06-2015, 09:19 PM #2
    yay_excel
    yay_excel is offline
    Forum Contributor yay_excel's Avatar
    Join Date
    08-12-2011
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    190

    Re: 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?
    Reply With QuoteRegister To Reply
  3. 05-06-2015, 09:22 PM #3
    plasma33
    plasma33 is offline
    Forum Contributor
    Join Date
    06-20-2011
    Location
    Auckland, NZ
    MS-Off Ver
    Excel 2013
    Posts
    165

    Re: Countif until blank cell (mutiple blank cells)

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

    Cheers
    plasma33
    Reply With QuoteRegister To Reply
  4. 05-06-2015, 09:29 PM #4
    yay_excel
    yay_excel is offline
    Forum Contributor yay_excel's Avatar
    Join Date
    08-12-2011
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    190

    Re: Countif until blank cell (mutiple blank cells)

    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
    Reply With QuoteRegister To Reply
  5. 05-06-2015, 09:30 PM #5
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,598

    Re: Countif until blank cell (mutiple blank cells)

    attach a Workbook
    (please do not post pictures or links to worksheets)
    • Click Advanced (next to quick post),
    • Scroll down until you see "Manage Attachments",
    • Click that then select "add files" (top right corner).
    • Click "Select Files" find your file, click "open" click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Click "Done" at bottom right to close the Attachment Manager.
    • Click "Submit Reply"
    Ben Van Johnson
    Reply With QuoteRegister To Reply
  6. 05-06-2015, 09:36 PM #6
    plasma33
    plasma33 is offline
    Forum Contributor
    Join Date
    06-20-2011
    Location
    Auckland, NZ
    MS-Off Ver
    Excel 2013
    Posts
    165

    Re: Countif until blank cell (mutiple blank cells)

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

    Cheers
    plasma33
    Reply With QuoteRegister To Reply
  7. 05-06-2015, 09:44 PM #7
    yay_excel
    yay_excel is offline
    Forum Contributor yay_excel's Avatar
    Join Date
    08-12-2011
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    190

    Re: Countif until blank cell (mutiple blank cells)

    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.
    Reply With QuoteRegister To Reply
  8. 05-06-2015, 09:48 PM #8
    plasma33
    plasma33 is offline
    Forum Contributor
    Join Date
    06-20-2011
    Location
    Auckland, NZ
    MS-Off Ver
    Excel 2013
    Posts
    165

    Re: Countif until blank cell (mutiple blank cells)

    I have uploaded a short version of my file with 2 sequences...thanks guys!!
    Reply With QuoteRegister To Reply
  9. 05-06-2015, 10:18 PM #9
    yay_excel
    yay_excel is offline
    Forum Contributor yay_excel's Avatar
    Join Date
    08-12-2011
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    190

    Re: Countif until blank cell (mutiple blank cells) 

    Please Login or Register  to view this content.
    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.
    Reply With QuoteRegister To Reply
  10. 05-06-2015, 10:52 PM #10
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Countif until blank cell (mutiple blank cells)

    In which cells do you want the output?

    I put this formula in XFA1:XFD17.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 05-06-2015, 11:02 PM #11
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,459

    Re: Countif until blank cell (mutiple blank cells)

    With helper column A to display sequence#, try as attachment.
    Attached Files Attached Files
    Quang PT
    Reply With QuoteRegister To Reply
  12. 05-07-2015, 01:01 AM #12
    plasma33
    plasma33 is offline
    Forum Contributor
    Join Date
    06-20-2011
    Location
    Auckland, NZ
    MS-Off Ver
    Excel 2013
    Posts
    165

    Re: Countif until blank cell (mutiple blank cells)

    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
    Reply With QuoteRegister To Reply
  13. 05-07-2015, 01:14 PM #13
    yay_excel
    yay_excel is offline
    Forum Contributor yay_excel's Avatar
    Join Date
    08-12-2011
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    190

    Re: Countif until blank cell (mutiple blank cells)

    You're welcome.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] leaving blank cells blank in dragging formula, combining different formulas in one cell.
    By camadax in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-15-2014, 02:36 AM
  2. [SOLVED] How to COUNTIF cells where data is below a date or the cell is blank?
    By cpyter in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-11-2014, 12:41 PM
  3. Skipping blank cells while copying and pasting in the first blank cell of Column B
    By src16 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-05-2013, 09:22 AM
  4. Multiplying the contents of a cell by a multiplier, leaving blank cells blank.
    By SirinKiz in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-28-2013, 01:10 AM
  5. Find a blank cell and calculate the average of all cells above till the next blank
    By joshnathan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-27-2012, 11:51 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1