I have excel 2003, what will be the alternate for countifS

[ThrottleWorks]

Hi,


I am using excel 2003, I want to know what will be the alternate for countifS.


I have also given the example below, Column A Status, Column B Product Type

I want to take countif for values matching "Sachin" & "Tendulkar" without using countifs.


Status ProductType

Sachin Tendulkar

Sachin Tendulkar

Saurav Ganguly

Saurav Ganguly

Saurav Ganguly

Saurav Ganguly

Virat Tendulkar

Virat Tendulkar

Virat Tendulkar

Sachin Tendulkar

Sachin Tendulkar

Sachin Tendulkar

Sachin Tendulkar


I do not want to use concat & VBA.

Anybody please help.

 

[NARAYANK991]

Hi Sachin ,


Can you try the following :


1. =SUMPRODUCT((A1:A13="Sachin")*(B1:B13="Tendulkar"))


2. =SUM(IF((A1:A13="Sachin")*(B1:B13="Tendulkar"),1,0)) entered as an array formula , using CTRL SHIFT ENTER.


I assume that your data is in cells A1 through B13 , i.e. there are no headers ; if there are headers , change the above addresses to A2:A14 and B2:B14.


Narayan

 

[prasaddn]

Hi Sachin,


You might try sumproduct() it is always much powerful than countifs().


you can know explanations from following links:


http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/

http://chandoo.org/wp/tag/sumproduct/

http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html


Formula Forensics. No 007 – Sumproduct

Posted: 21 Dec 2011 12:30 AM PST


Regards,

Prasad DN

 

[ThrottleWorks]

Hi Prasad,


Wonderfull, this worked !


Thanks a lot Sir.