+ Reply to Thread
Results 1 to 4 of 4

CountIFS roll up to one value- Sum the one time month is called out

  1. #1
    Registered User
    Join Date
    02-13-2018
    Location
    Seattle, WA USA
    MS-Off Ver
    2013
    Posts
    59

    CountIFS roll up to one value- Sum the one time month is called out

    I am looking for a formula that will Count the month (A) for any "x" value in (L). And count that month within the year column (B).

    So for example is (A) counts "1" as the value in 2017. But let's say there are 30 counts of "1" in (A). I just want in another cell the formula to return the "1" where the value equals "x" in column (I). Something where I can drag the formula down and hopefully it will look just return the first time a month is called out in (A) where there is an "x" in column (L).

    similar to what the pivot is doing below which I want to get rid of.

    It is counting months 1-12, but only returning a count with the cells that have values.

    In the case of the pivot, the cells with values are sums on column (I) in this data tab.

    I have this formula in there now, but it won't work in full like I need it to.

    Please Login or Register  to view this content.

  2. #2
    Forum Contributor
    Join Date
    06-05-2017
    Location
    Belem, Brazil
    MS-Off Ver
    2016
    Posts
    183

    Re: CountIFS roll up to one value- Sum the one time month is called out

    Not sure I get it.
    Please let us know the expected result in the first few rows.

  3. #3
    Registered User
    Join Date
    02-13-2018
    Location
    Seattle, WA USA
    MS-Off Ver
    2013
    Posts
    59

    Re: CountIFS roll up to one value- Sum the one time month is called out

    Column A and column B have the year and month. So what I want to do is put a formula in Cell AA2 (on earlier attachment) that returns just once that the the month is called out within a year. So example (A2) is "1" for January, but there are multiple values of "1" withing the year column, example "1" in "2017". So I want the formula to pick up only once when the month is called out in a year.

  4. #4
    Forum Contributor
    Join Date
    06-05-2017
    Location
    Belem, Brazil
    MS-Off Ver
    2016
    Posts
    183

    Re: CountIFS roll up to one value- Sum the one time month is called out

    Try this:

    =IF(COUNTIFS(I$2:I2,"x",A$2:A2,A2,B$2:B2,B2)=1,COUNTIFS(I$2:I2,"x",A$2:A2,A2,B$2:B2,B2),"")

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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