+ Reply to Thread
Results 1 to 7 of 7

Problem with my countifs sum

  1. #1
    Registered User
    Join Date
    09-24-2014
    Location
    New York City , USA
    MS-Off Ver
    2010
    Posts
    4

    Problem with my countifs sum

    Hello ,

    I wrote the following code to sum the number of "yes" in one column of a row that also has the value of 201303

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    this formula works fine.

    But when I change the formula to sum the number of "no" ...i get an error reading of div/0 even though i know that is not the case
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Can someone help me out here...

    Thanks

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

    Re: Problem with my countifs sum

    Since you have Excel 2010

    looks like you are looking for an average?
    =AVERAGEIF(Sheet1!$B$2:$B$4148,"yes",Sheet1!$T$2:$T$4148)
    or
    =AVERAGEIF(Sheet1!$B$2:$B$4148,"no",Sheet1!$T$2:$T$4148)
    Does that work for you?

    It's hard to follow what is in what column. I believe it's yes/no in B, numbers to be summed or averaged in T. What's in H and how does that fit in?
    An example workbook would help (Go advanced>manage Attachments)
    Last edited by ChemistB; 09-24-2014 at 11:54 AM.
    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

  3. #3
    Registered User
    Join Date
    09-24-2014
    Location
    New York City , USA
    MS-Off Ver
    2010
    Posts
    4

    Re: Problem with my countifs sum

    B H S T
    201403 775254 No No
    201403 775254 No No
    201403 775613 No No
    201403 775613 No No
    201403 No No
    201403 No No
    201403 No No
    201403 No No
    201403 No No
    201403 No No
    201303 774371 Yes Yes
    201303 774371 Yes Yes
    201303 774371 Yes Yes
    201303 774371 Yes Yes

    Here is a sample of the table i am working on ....

  4. #4
    Forum Contributor
    Join Date
    02-27-2004
    Location
    California, United States
    MS-Off Ver
    Excel 2016
    Posts
    315

    Re: Problem with my countifs sum

    Not being clear on which column holds the "yes" or "no" value and which column holds the 201303 value, have you considered using the sumifs statement?

  5. #5
    Registered User
    Join Date
    09-24-2014
    Location
    New York City , USA
    MS-Off Ver
    2010
    Posts
    4

    Re: Problem with my countifs sum

    Column T holds the "yes/no "
    Column B holds the 201303 value
    Column H holds a value was well

    So I am basically trying to get a Sum of all the "No" in T who's rows have 201303 in B AND H is not blank

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

    Re: Problem with my countifs sum

    Try this

    =COUNTIFS(Sheet1!$T$2:$T$4148,"No",Sheet1!$B$2:$B$4148,201303,$H$2:$H$4148,"<>"&"""")

  7. #7
    Registered User
    Join Date
    09-24-2014
    Location
    New York City , USA
    MS-Off Ver
    2010
    Posts
    4

    Re: Problem with my countifs sum

    Oh...i forgot to mention that H has duplicate entries....so it will be the number of unique entries of H along with the other parameters

+ 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. [SOLVED] problem with countifs
    By Lee Ginther in forum Excel General
    Replies: 8
    Last Post: 08-14-2014, 09:02 AM
  2. [SOLVED] Problem using Countifs
    By pamc in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-07-2014, 04:32 PM
  3. [SOLVED] problem with countifs
    By albert28 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-27-2014, 11:16 PM
  4. Problem with using countifs
    By alb2p in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-14-2013, 01:36 PM
  5. Replies: 0
    Last Post: 12-16-2011, 09:01 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