+ Reply to Thread
Results 1 to 6 of 6

Counting blank cells that are empty buy contain a formula...

  1. #1
    Forum Contributor
    Join Date
    08-29-2013
    Location
    CT
    MS-Off Ver
    Excel 2016 Mac
    Posts
    268

    Counting blank cells that are empty buy contain a formula...

    So i have my summer school attendance sheet that i need to calculate the % of students who have shown up for each program but i want the formula to update daily as i input the attendance. The problem i am running into is that the formula is counting the cells with a formula in them but with no number as a non blank.

    This is the formula that i have been using =(SUM(C3:V3))/(B3*COUNTA(C3:V3))

    I have attached a sheet with my data on one tab and my desired results on the other.

    Any help would be greatly appreciated.

    Los
    Attached Files Attached Files

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Counting blank cells that are empty buy contain a formula...

    The problem is that the formulas are NOT returning ""
    They're returning 0's.
    But you have the cells custom formatted to Hide the 0's.

    Try
    =(SUM(C3:V3))/(B3*COUNTIF(C3:V3,"<>0"))

  3. #3
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Counting blank cells that are empty buy contain a formula...

    Try

    =(SUM(C3:V3))/(B3*COUNTIF(B14:V14,">0"))
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Counting blank cells that are empty buy contain a formula...

    FYI,
    Your numbers are incorrect because there are 17 numbers, not 18.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Forum Contributor
    Join Date
    08-29-2013
    Location
    CT
    MS-Off Ver
    Excel 2016 Mac
    Posts
    268

    Re: Counting blank cells that are empty buy contain a formula...

    Thank you both, both solutions worked wonders.

    Los

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Counting blank cells that are empty buy contain a formula...

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. formula for name order switch that leaves empty cells blank
    By redzan in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-17-2013, 07:01 PM
  2. Replies: 5
    Last Post: 01-12-2013, 12:16 AM
  3. Averageif again formula is counting blank cells
    By AllenMead in forum Excel General
    Replies: 5
    Last Post: 09-22-2010, 02:08 AM
  4. Date formula counting blank cells
    By kerndogg in forum Excel General
    Replies: 2
    Last Post: 12-09-2009, 02:13 PM
  5. =SUMPRODUCT formula is counting the blank cells as well as zero's
    By JR in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-16-2006, 11:45 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1