Countif function for names

[vamsi]

Hi,
Kindly help me with the countif function where I am using Count if for calculating the country name and the count of status as Open, Close, Ongoing.
I am using two countif function for getting the count however it is showing negative number.

Attached is the test case.

Regards,
Vamsi

 

[Gangadhar Yeligaty]

Hi Vamsi,

Pls see Attached file

Regards,
Gangadhar Yeligaty

 

[Khalid NGO]

Hi,
Another option:
=SUMPRODUCT(($A$11:$A$16=B$2)*($B$11:$B$16=$A3))

you need to make status entries = site entries
e.g: "Close" in both

Regards,

 

[Asheesh]

Just scooted through the responses given above...just want to highlight that you should always choose countifs over sumproduct....countifs or sumifs are faster compared to sumproduct...

 

[Khalid NGO]

Hi Asheesh, Thanks mate,
You are right but i think OP data is very small; so, will be there any diff?

 

[Asheesh]

Bhai, This was just an headsup incase data is huge, otherwise for the kind of dataset in the attached..it hardly matters...

Go through the below link for more details..

http://msdn.microsoft.com/en-us/lib...ice2007excelperf_ExcelPerformanceImprovements

 

[skyh3ck]

countif does not work here, you need to use =COUNTIFS(...

like this

=COUNTIFS($A$11:$A$16, B$2, $B$11:$B$16, $A$3)

also check the file from Gangadhar,

 

[skyh3ck]

also you have put the same formula "countif" for vote above,

i think you are trying to put Countif and Countifs, any way hope you found your answer here

 

[vamsi]

Hi Gangadhar,

Thanks for your formula, It is working.

 

[Khalid NGO]

Bhai, This was just an headsup incase data is huge, otherwise for the kind of dataset in the attached..it hardly matters...

Go through the below link for more details..

http://msdn.microsoft.com/en-us/lib...ice2007excelperf_ExcelPerformanceImprovements

Thanks brother for the link and explanation.
Take care.