# COUNTIF in PowerPivot



## PierreZ

I'm trying to accomplish the same as with a normal COUNTIF function in Excel but in PowerPivot. The function is =COUNTIF(A:A,A1).

The result should look like this:


ItemsCount_Distinct_ItemB180030.989.L2B180030.989.M1B180030.989.XL2B180030.001.L1B180030.001.M2B180030.001.S1B180030.001.M2B180030.989.L2B180030.989.XL2B180030.989.XXL1

<colgroup><col><col></colgroup><tbody>

</tbody>


----------



## butlerrbrian

This sounds like a job for =CALCULATE(COUNTROWS(DISTINCT([Column])), conditions of the IF you desire<filters for="" if="" conditions=""><filters>)





PierreZ said:


> I'm trying to accomplish the same as with a normal COUNTIF function in Excel but in PowerPivot. The function is =COUNTIF(A:A,A1).
> 
> The result should look like this:
> 
> 
> ItemsCount_Distinct_ItemB180030.989.L2B180030.989.M1B180030.989.XL2B180030.001.L1B180030.001.M2B180030.001.S1B180030.001.M2B180030.989.L2B180030.989.XL2B180030.989.XXL1
> 
> <tbody>
> 
> </tbody>


</filters></filters>


----------



## PierreZ

Hi Butlerrbrian

Thanks for the reply, however, what would be the if condition for counting the amount of unique values in the Items column? I cant seem to get it right. It keeps giving me an error.

My formula looks like this: =CALCULATE(COUNTROWS([ITEMS]),[ITEMS]=[ITEMS])


----------



## MD610

COUNTROWS() only accepts tables as an arguement.  To count a column you must use either COUNT() or COUNTA().

That being said, this should give you the result you are looking for:



		Code:
__


=CALCULATE(COUNTROWS(TableName), ALLEXCEPT(TableName, TableName[Items]))


----------



## PierreZ

I also found this solution in the interim. Thanks though. Works perfectly!


----------



## Legacy 275626

MD610 said:


> COUNTROWS() only accepts tables as an arguement.  To count a column you must use either COUNT() or COUNTA().
> 
> That being said, this should give you the result you are looking for:
> 
> 
> 
> Code:
> __
> 
> 
> =CALCULATE(COUNTROWS(TableName), ALLEXCEPT(TableName, TableName[Items]))



Hi there

does this only work on a one column powerpivot database?

I'm trying to accomplish the same result, I have a small database that's pulled in from Access. It contains data on patients, their hospital attendances, which hospital, etc. What I would like to do is add columns where I can calculate:
a) the count of attendances by that patient ID (so just one condition like the countif(A:A,A1) example above)
b) count of attendance by that patient in a given year
c) count of attendance by a patient in that year at a particular hospital.

I've tried the allexcept example given, where I selected all columns in the table except the patientID and it didn't work.

Any help would be much appreciated.

Mejd


----------



## EVG47

mejdnuaman said:


> Hi there
> 
> does this only work on a one column powerpivot database?
> 
> I'm trying to accomplish the same result, I have a small database that's pulled in from Access. It contains data on patients, their hospital attendances, which hospital, etc. What I would like to do is add columns where I can calculate:
> a) the count of attendances by that patient ID (so just one condition like the countif(A:A,A1) example above)
> b) count of attendance by that patient in a given year
> c) count of attendance by a patient in that year at a particular hospital.
> 
> I've tried the allexcept example given, where I selected all columns in the table except the patientID and it didn't work.
> 
> Any help would be much appreciated.
> 
> Mejd




For those who googled the countif problem in PowerPivot and ended in this thread: The provided formula should work if you replace all comma's ( , ) with a semicolon ( ; ).


----------



## mimieuxy

Hi everyone!

I have encountered the exactly same issue except that I have no powerpivot option in my add-in... 
I'm essentially trying to achieve the same result as the OP did, would anyone be able to advise on how I can do this in normal pivot table? Thanks, will be much appreciated!!!


----------



## AnnaH

mimieuxy said:


> Hi everyone!
> 
> I have encountered the exactly same issue except that I have no powerpivot option in my add-in...
> I'm essentially trying to achieve the same result as the OP did, would anyone be able to advise on how I can do this in normal pivot table? Thanks, will be much appreciated!!!



Just use the =COUNTIF on the dataset before you Pivot it


----------

