1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

countif statement

Discussion in 'Business Applications' started by cdevaro, Nov 14, 2001.

Thread Status:
Not open for further replies.
Advertisement
  1. cdevaro

    cdevaro Thread Starter

    Joined:
    Aug 19, 1999
    Messages:
    99
    I am trying to get an average of the number of entries in a group of cells that are not listed together. I am currently using a sum then a countif statement (advise me if another one would be better) to count the number of entries and divide the sum by the number of the counted entries larger than 1. The cells contain a formula. The problem is the cells are not connected so I need to countif the cells individually. Example:

    (A1) (B1) (C1) (D1) (E1)
    13 12 countif(A1+C1,">1")

    I need to count cells A & C, but not B. This is in several rows of data. Listing the cells separately gives an error.
    What do I need to do to count the number of cells that contain numbers?
     
  2. drdrew1469

    drdrew1469

    Joined:
    Nov 30, 2000
    Messages:
    436
    I think I might understand what you're trying to do... I think. :rolleyes:

    =SUM(A1:E1)/SUMIF(A1:E1,">1")

    Give that a shot. It will sum all of A1:E1 then divide the total by the total of everything >1

    ie.

    a b c d e
    1 2 3 4 5

    =15/14

    Hope that helps,

    Drew
     
  3. drdrew1469

    drdrew1469

    Joined:
    Nov 30, 2000
    Messages:
    436
    The only other thing I can think that you would want to do would be this:

    =SUM(A1:E1)/COUNTIF(A1:E1,">1")

    a b c d e
    1 2 3 4 5

    =15/4

    Let me know,

    Drew
     
  4. cdevaro

    cdevaro Thread Starter

    Joined:
    Aug 19, 1999
    Messages:
    99
    The problem with this is that I don't want to include the data in certain cells. I have different data in consecutive columns for different tickets, but I want to add certain data for several tickets if a ticket is entered.

    ticket1 ticket2 ticket3 average
    a b c a b c a b c of b

    But I may only have two tickets entered.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/58544

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice