Count number of non-blank cells in rows and columns

  • Hi,


    I am trying to come up with a formula that will count the number of non blank cells in each row and compare it to the number of non-blank cells in each column and decide if it should add them up. Let me give an example of a spreadsheet I am using and then try to explain it further.[TABLE="class: grid, width: 500"]

    [tr]


    [td]

    Month

    [/td]


    [td]

    Person 1

    [/td]


    [td]

    Person 2

    [/td]


    [td]

    Person 3

    [/td]


    [td]

    Person 4

    [/td]


    [td]

    Person 5

    [/td]


    [td]

    Person 6

    [/td]


    [td]

    Person 7

    [/td]


    [td]

    Person 8

    [/td]


    [td]

    Person 9

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    100

    [/td]


    [td]

    100

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    100

    [/td]


    [td]

    50

    [/td]


    [td]

    100

    [/td]


    [td]

    100

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    100

    [/td]


    [td]

    50

    [/td]


    [td]

    50

    [/td]


    [td]

    150

    [/td]


    [td]

    100

    [/td]


    [td]

    100

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    100

    [/td]


    [td]

    50

    [/td]


    [td]

    50

    [/td]


    [td]

    50

    [/td]


    [td]

    100

    [/td]


    [td]

    50

    [/td]


    [td]

    100

    [/td]


    [td]

    100

    [/td]


    [td]

    100

    [/td]


    [/tr]


    [/TABLE]
    Let's say I have the table above. I want to add up the number of blank cells in each row but I only want to include the cells in the count if the number on non-blank cells in a column is less than or equal to 3. For example, for the first month, the number of non blank cells to count is 2. For the second month, the number of non blank cells is 4. For the third month, the number of non-blank cells is 6. However, for the fourth month, the number of non-blank cells is seven because the columns for person 1 and 2 have 4 rows worth of data.


    In addition, I also want to have a way to use this same criteria to sum up the values in the corresponding cells. For example, for the first month, the total is 100. For the second month, the total would be 350. For the third month, the total would be 550. But for month 4, the total would also be 550 because the first 2 columns are excluded.

  • Re: Count number of non-blank cells in rows and columns


    Your explanation is confusing.

    Quote

    I only want to include the cells in the count if the number on non-blank cells in a column is less than or equal to 3


    For Month 1 the 2 cells that are not empty are in a column that has 4 non-blank cells, so, why is the required count not 0?


    For Month 2, with the 4 cells that are not empty 2 are in a column that has 4 non-blank cells, so, why is the required count not 2?


    For Month 3, with the 6 cells that are not empty 2 are in a column that has 4 non-blank cells, so, why is the required count not 4?


    The condition you stated would appear to apply only to month 4 in your stated desired results.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Count number of non-blank cells in rows and columns



    Those are all good questions and looking back I realized I made a few mistakes in my wording. The required count should be 2 every month according to the sample data I provided, however, I do not just want to type in 2 everywhere where I need this data because let's say I change one month and now the required count is 3. I want it to dynamically change. To explain this and simultaneously answer your questions, lets see if this makes sense. For Month 1, you're right about the two non-blank cells being in columns that contain 4 non-blank cells. But, for Month 1, I'm only concerned with Month 1 data (i.e. look across the row, see 2 non blank cells, check those columns and see this is the first non-blank cell in each, answer is required count of 2). For Month 2, I'm only concerned with Month 1 and 2 for data. Look across the row, see 4 non-blank cells, compare to the previous month and see that two of the cells for Month 2 also have data for Month 1, the required count is again 2. Can you see the pattern now? Hopefully this makes sense.

  • Re: Count number of non-blank cells in rows and columns


    Still a bit confusing.


    Can you attach your workbook showing the desired results (manually enter those in the cells where you want them to be displayed) and explain the logic behind each result.
    [sw]*[/sw]

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Count number of non-blank cells in rows and columns


    Quote from KjBox;795089

    Still a bit confusing.


    Can you attach your workbook showing the desired results (manually enter those in the cells where you want them to be displayed) and explain the logic behind each result.
    [sw]*[/sw]


    forum.ozgrid.com/index.php?attachment/72963/


    I may have come up with a solution to my problem but I'll run it by you first. Since every person will have data from their first month onward, even if it's zero, can I just used the Count function to count the number of filled cells in a particular row and then subtract it from the number of filled cells in the previous row to get the number of new cells that are filled?


    As for the distinction between the totals for returning and new people, I want a way for the spreadsheet to tell me what the total is by adding up just the values corresponding to returning people in a month and a separate column telling me the total amount pertaining to the new people. 9/10 the value for the month corresponding to each individual person will be 100 and therefore one would assume that I can just go ahead and say ok we have 2 new people this month so the sum is 200. However, I need this to be dynamic in the sense that if I have one person whose total is 100 and another who is 150, the spreadsheet isn't just going to assume it's as simple as 100*2 but would actually be 100+150.


    I have manually entered in the information as it should be calculated into the example spreadsheet.


    Does all of this make sense?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!