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

1. ## 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. ## 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. ## 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. ## 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),"")

#### Thread Information

##### Users Browsing this Thread

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

#### 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