=COUNTIFS formula in Excel 2003

[atulrajratna]

Hi team,


We have a worksheet where we collate the data by using COUNTIFS formula. It's working with excel 2007 but not in 2003.


Do we have any option or any another formula which will work like COUNTIFS in excel 2003? In which we can give more criteria to count the numbers.


Thanks!

-Atul Rajratna

 

[Faseeh]

Hi Atul Rajratna,


One method could be to use Sumproduct() function. Taking the Example that is provided for countifs() in excel 2007 help, you can count for "yes" or "no" with sumproduct(). With your data located in A1:D4...


Countifs() Works as =COUNTIFS(B2:B5,"=Yes",C2:C5,"=Yes")

Sumproduct() Works as =SUMPRODUCT(((B2:B5)="yes")*((C2:C5)="yes"))


In both case the result is 2 see the file on this link...

http://www.4shared.com/file/V5motPwe/Atul_Rajratna.html

http://www.4shared.com/file/V5motPwe/Atul_Rajratna.html

(xls & xlsx sheets which ever your Excel Support)


Regards,

Faseeh

 

[SirJB7]

Hi, Atul Rajratna!

Here it was the workaround I told you. The SUMPRODUCT function. And I know two guys that may be laughing at me while reading this, and they're right!

Thanks, Faseeh, you've helped both Atul Rakratna and me!

Regards!

 

[Faseeh]

Hi Sir SirJB7,


Your feedback is always worth reading for u always come up with novel ideas. Your remarks always boost my moral. Thank you..


Faseeh

 

[SirJB7]

Hi, Faseeh!

Very kind of you, thanks.

Regards!

 

[atulrajratna]

Thank you so much all..

for your endless support n for everything.. you always help me, teach me..

u cant imagine how my excel knowledge has developed.. Its just because of you..

Hats off to you all.. Hats off to Chandoo.org & the team. Thanks again!

 

[Faseeh]

Hi Atul Rajratna,


You are always welcome!


Faseeh