New to countif & struggling

[AllyOops]

Hi I got some great help on countif a few weeks back! I tried to build on that advice and I am stuck. I would like to better understand why this isnt working:

=IF(AG3="Modify",COUNTIF($AA$2:$AA$100000,AA2),"")>1,"Escalate","SomethingElse")

so I tried to simplify with this

=COUNTIF(($AA$2:$AA$100000,AA2)>1,"Escalate","SomethingElse")

And I am still getting an error....I am missing something here and would like to better understand.

I am trying to look at AG=Modify only and return the word escalate if I have multiple hits in column AA!

Thanks!

 

[Robert Mika]

Try:
=IF(AND(AG3="Modify",COUNTIF($AA$2:$AA$100000,AA2)>1),"Escalate","SomethingElse")
or
=IF(AG3="Modify",IF(COUNTIF($AA$2:$AA$100000,AA2)>1,"Escalate","SomethingElse"))

 

[AllyOops]

Thanks Robert!

 

[arvindkrkaushik]

Hi,

Can anyone help me on counting this?: I want to count as given below the value should be displayed only once rest should show 0.

Location</SPAN>	Count</SPAN>
Adoni - Spoke</SPAN>	3</SPAN>
Adoni - Spoke</SPAN>	0</SPAN>
Adoni - Spoke</SPAN>	0</SPAN>
Agra - Sanjay Place</SPAN>	4</SPAN>
Agra - Sanjay Place</SPAN>	0</SPAN>
Agra - Sanjay Place</SPAN>	0</SPAN>
Agra - Sanjay Place</SPAN>	0</SPAN>
Agra - Sanjay Place</SPAN>	0</SPAN>
Agra - Sanjay Place Many thanks in advace.... Arvind Kushik</SPAN>	0</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

 

[arvindkrkaushik]

ADVERTISEMENT

Hi Robert,

Please suggest me on this...Also Last two values pasted by mistake (these are only 4 times.."Agra - Sanjay Place"...

Thanks,
Arvnd

 

[Robert Mika]

Thanks Robert!

You are welcome.

 

[Marcelo Branco]

ADVERTISEMENT

Hi Arvind,

I think the correct value for Agra - Sanjay Place is 6, not 4

If so, try this in B2
=COUNTIF($A$2:$A$10,A2)*(COUNTIF($A$2:$A2,A2)=1)
copy down

M.

 

[Robert Mika]

Excel 2010

	A	B
1	Location	Count
2	Adoni - Spoke	3
3	Adoni - Spoke	0
4	Adoni - Spoke	0
5	Agra - Sanjay Place	6
6	Agra - Sanjay Place	0
7	Agra - Sanjay Place	0
8	Agra - Sanjay Place	0
9	Agra - Sanjay Place	0
10	Agra - Sanjay Place	0

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B2 =IF(COUNTIF($A$2:A2,A2)=1,COUNTIF($A$2:$A$10,A2),0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[arvindkrkaushik]

Thanks a lot Robert...That is wonderfull..It amazing....!!!!

Arvind

 

[Robert Mika]

You are welcome.
Thanks for feedback