Countifs

[Anonymous]

Hi there,

I have been struggling for a while, and have looked through many forum posts to try and help me convert my Excel COUNTIFS to Power BI, however, nothing so far has worked for me. It may be that I'm just being a bit stupid so apologies if that's the case. 

This is the formula I have (I am aware it's long and there's probably an easier/better way I could have done what I'm trying to do, but please bear with me):

=COUNTIFS(Sheet1!$Y$2:$Y$1048576,”Word1”,Sheet1!$C$2:$C$1048576,IF(‘Sheet2’!$C$5=“Word2”,”<>”&””,’Sheet2’!$C$5),Sheet1!$D$2:$D$1048576,IF(‘Sheet2’!$C$7=“Word2”,”<>”&””,’Sheet2’!$C$7),Sheet1!$E$2:$E$1048576,IF(‘Sheet2’!$C$8=“Word2”,”<>”&””,’Sheet2’!$C$8),Sheet1!$G$2:$G$1048576,IF(‘Sheet2’!$C$10=“Word2”,”<>”&””,’Sheet2’!$C$10),Sheet1!$H$2:$H$1048576,IF(‘Sheet2’!$C$11=“Word2”,”<>”&””,’Sheet2’!$C$11),Sheet1!$J$2:$J$1048576,IF(‘Sheet2’!$C$13=“Word2”,”<>”&””,’Sheet2’!$C$13),Sheet1!$K$2:$K$1048576,IF(‘Sheet2’!$C$15=“Word2”,”<>”&””,’Sheet2’!$C$15),Sheet1!$L$2:$L$1048576,IF(‘Sheet2’!$C$17=“Word2”,”<>”&””,’Sheet2’!$C$17),Sheet1!$M$2:$M$1048576,IF(‘Sheet2’!$C$18=“Word2”,”<>”&””,’Sheet2’!$C$18),Sheet1!$O$2:$O$1048576,IF(‘Sheet2’!$C$20=“Word2”,”<>”&””,’Sheet2’!$C$20))+COUNTIFS(Sheet1!$W$2:$W$1048576,”Word3”,Sheet1!$C$2:$C$1048576,IF(‘Sheet2’!$C$5=“Word2”,”<>”&””,’Sheet2’!$C$5),Sheet1!$D$2:$D$1048576,IF(‘Sheet2’!$C$7=“Word2”,”<>”&””,’Sheet2’!$C$7),Sheet1!$E$2:$E$1048576,IF(‘Sheet2’!$C$8=“Word2”,”<>”&””,’Sheet2’!$C$8),Sheet1!$G$2:$G$1048576,IF(’Sheet2’!$C$10=“Word2”,”<>”&””,’Sheet2’!$C$10),Sheet1!$H$2:$H$1048576,IF(‘Sheet2’!$C$11=“Word2”,”<>”&””,’Sheet2’!$C$11),Sheet1!$J$2:$J$1048576,IF(‘Sheet2’!$C$13=“Word2”,”<>”&””,’Sheet2’!$C$13),Sheet1!$K$2:$K$1048576,IF(‘Sheet2’!$C$15=“Word2”,”<>”&””,’Sheet2’!$C$15),Sheet1!$L$2:$L$1048576,IF(‘Sheet2’!$C$17=“Word2”,”<>”&””,’Sheet2’!$C$17),Sheet1!$M$2:$M$1048576,IF(‘Sheet2’!$C$18=“Word2”,”<>”&””,’Sheet2’!$C$18),Sheet1!$O$2:$O$1048576,IF(‘Sheet2’!$C$20=“Word2”,”<>”&””,’Sheet2’!$C$20))

Any help would be much appreciated.

Thanks and kind regards.

[v-piga-msft]

Hi @Anonymous,

Actually, there are no concepts of cells and rows like Excel in Power BI. 

Based on your function, it seems that you have 2 tables and your formula is too long and a little complex.

If it is convenient, could you share your data sample and your desired output in Power BI so that I can understand your scenario better and get the solution.

Best Regards,

Cherry

[Anonymous]

Hi,@v-piga-msft,

Thanks for your response.

So simply, I'm looking to count the number of times Sheet 1 Column Y says "Word 1", if Sheet 1 Column C matches Sheet 2 C5, and if Sheet 2 C5 says "Word2" then count all in column Y. And that is pretty much repeated for 9 other columns in Sheet 1 and 2. 

It is worth noting that in Sheet 2 all the cells that I'm using are dropdown lists. However, am I right in thinking I will have to use the raw dropdown data instead?

Overall, I'm looking to get an output of a value in power bi that I can make into a chart alongside similar calculations. 

I am attaching an example, although I have only included a snapshot because it is a huge table and have changed all the words. Hopefully this makes it a little easier to understand. For example, in the picture, I'm trying to count the number of times column J says Word1 (this is Sheet1)

This is an edited version of Sheet2:

Really appreciate all your help.

Thanks and kind regards.