Platform

 View Only

 Excel COUNTIF equivalent in TBM Studio

Jump to  Best Answer
  • BillTheTBMGuy
  • TBMStudio
Devon Bilsing's profile image
Devon Bilsing posted Fri March 10, 2023 04:45 PM

Hi, 

Is there a function in Apptio to achieve this outcome of 'counting' similar to COUNTIF in excel? Sumif() returns total for each line not a progression of counts. Notice myUser1. 

User ID Count
myUser1 1
myUser2 1
myUser1 2
myUser1 3
myUser3 1

#BillTheTBMGuy
#TBMStudio
Dan Kelly's profile image
Dan Kelly posted Mon March 13, 2023 11:16 AM  Best Answer

You can do it like this:

Be careful not to use any of the above in an object identifier.  Anything with row() or anything derived from row() can lead to performance issues when used in object identifiers.  Doesn't seem like you would end up doing that in this case, but I always provide the disclaimer. 


#TBMStudio
Dan Kelly's profile image
Dan Kelly posted Fri March 10, 2023 04:47 PM

You can use sumif like this:

=sumif(User ID,User ID,1)

What you will get back is a count of the rows with the same User ID.


#TBMStudio
Devon Bilsing's profile image
Devon Bilsing posted Fri March 10, 2023 05:25 PM

@Dan Kelly if I do that it’ll return “3” for every line of myuser1. I’m trying to achieve in this case a count of occurrences. First line for myuser1 returns 1. Second returns 2 and third returns 3. For however many times myuser1 appears. 


#TBMStudio
Jenny Franklin's profile image
Jenny Franklin posted Fri March 10, 2023 05:42 PM

Nix what I just posted - so you're not wanting a total or a count in the way I was interpreting it - you want a progressive count - okay, back to the drawing board as they say 😉

Hey @Devon Bilsing!  I just tried Daniel's logic and was able to get it to work - here are the results.  Hope this helps! 😊


#TBMStudio
Jenny Franklin's profile image
Jenny Franklin posted Fri March 10, 2023 05:50 PM

@Devon Bilsing can you post your COUNTIF logic from Excel?  I must be using it the wrong way because it's working the SUMIF way... 🤦‍♀️


#TBMStudio
Dan Kelly's profile image
Dan Kelly posted Fri March 10, 2023 05:56 PM

Sorry, I was thinking sumif because that's more common.  Given that, I am going to take a step back and asking why you want to do this?


#TBMStudio
Guillermo Cuadrado's profile image
Guillermo Cuadrado posted Mon March 13, 2023 04:50 AM

I can't think of a way of doing this, @Devon Bilsing

@Devon Bilsing

The closest I got was this, by using the Row() function:

However, how to get to have the 1, 2, 3, ... series you want, i.e. how to get rid of the irrelevant rows in between?

I tried sorting the table in CT, but the engine remembers the original order:

Short of sorting the source data, I cannot think of a solution, and even then, it wouldn't work: how to reset the counter for the next category?

Even Bill is stumped...

 


#TBMStudio
#BillTheTBMGuy

Related Content