If-Sum then Count Distinct

[ksomosera10]

Hi!

Can you help me out on my problem?

I have this table and text box. Here's what I need. I want to show in my text box the distinct count of people having a specified criteria and condition.

Just to give you an idea, here's a sample data on my table:

|      Name    |  Power        |  Element |    HP    |

|    Batman  |  Batpower  |      Dark  | 1000    |

| Spiderman |    Web        |  Nature  |  900    |

| Aquaman  |Trident Strike|    Water  |  2000  |

On my text box I want to show this kind of expression:

If Element == 'Nature' and HP <= 1000 Then Count Distinct Name

I hope this helps you understand my problems...

Thanks!

[Kushal_Chawda]

may be this

=count(distinct  {<Element ={'Nature'},HP ={'<=1000'}>} Name)

[Anil_Babu_Samineni]

Try this?

If(Element = 'Nature' and HP <= 1000, Count(Distinct Name))

OR

Count({<Element = {Nature}, HP = {'<=1000'}>} DISTINCT Name)

[Kushal_Chawda]

or

=count(distinct  {<Name ={"=sum({<Element={'Nature'}>}HP)<=1000"}>} Name)

[ksomosera10]

Thanks kushal,

By the way. I forgot to mention these

If Element == 'Nature' and SUM(HP) <= 1000 Then Count Distinct Name

I've put underline on it. I also need to sum up the HP. because some heroes have different HP but same names depending on their levels.

[ksomosera10]

Thanks Anil,

I forgot to mention I need to sum the HP before I check if it is less than 1000.

If Element == 'Nature' and SUM(HP) <= 1000 Then Count Distinct Name

Thanks.

[Anil_Babu_Samineni]

This?

Count({<Element = {Nature}, HP = {"Sum(HP) <=1000"}>} DISTINCT Name)

[Anil_Babu_Samineni]

My bad, Missed equal sign

Count({<Element = {Nature}, HP = {"=Sum(HP) <=1000"}>} DISTINCT Name)

[ksomosera10]

Hi Anil,

Thanks for helping out but All of my text box return's 0

[Anil_Babu_Samineni]

May be you don't have data for that? Can you split expression like and tell us which expression returns the value?

Count(DISTINCT Name)

Count({<Element = {Nature}>} DISTINCT Name)

Count({<HP = {"=Sum(HP) <=1000"}>} DISTINCT Name)