COUNTIFS in Excel 2003?

[Bench]

Hi,

I worked out a solution to an issue i've had with an exel spreadsheet using the COUNTIFS, however when i've got to work we don't have excel 2007 and i've realised now that there is no COUNTIFS function in '03

Is there an add in that can be added to excel 03 or a function that can be vba'd?

These are my formulas:

=COUNTIFS(H:H,"rbs",I:I,"<4")
=COUNTIFS(H:H,"rbs",I:I,"=4")
=COUNTIFS(H:H,"rbs",I:I,">4")

Any help would be appreciated

 

[Sharathpai12]

I worked out a solution to an issue i've had with an excel spreadsheet using the COUNTIFS, however it was on excel 2007 but when i apply same in 2003,it does not give me result. After doing some research in Google, realized that there is no COUNTIFS function in excel 2003.

Can someone please help me to convert the below formula in usable format in excel 2003

=COUNTIFS(Tickets!$D:$D,'Resolver Group Dashboard'!$A2,Tickets!$I:$I,"Open")

Basically,I want to get the count of ticket against each resolver group/support team by comparing the data dump having status" Open".

 

[shg]

=sumproduct((Tickets!$D1:$D100 = 'Resolver Group Dashboard'!$A2) * (Tickets!$I1:$I00 = "Open"))

You don't want to use full column references.

 

[Sharathpai12]

But still am getting error message as Num in excel 2003...if you want i can share the sheet with you.

 

[RoryA]

What's the actual formula you are using now?

 

[Sharathpai12]

Formula used in 2007---> =COUNTIFS(Tickets!$D:$D,'Resolver Group Dashboard'!$A2,Tickets!$I:$I,"Open")

Formula used in 2003--> =sumproduct((Tickets!$D1:$D100 = 'Resolver Group Dashboard'!$A2) * (Tickets!$I1:$I00 = "Open"))

Basically,I want to get the count of ticket against each resolver group/support team by comparing two column in different sheets(one from the data dump and other from resolve name) having status" Open".

 

[RoryA]

You're missing a 1 in the I range:
Tickets!$I$1:$I$100

Also you should make the row numbers absolute in case you copy this down.

 

[karthi5565]

Thank you VoG!!!! It worked perfectly.

Hi,

Could anyone please help me on this...

I've used the same formula for my range but it prompts "the formula you typed contain erros".

=SUMPRODUCT(--(A2:A1845,"WR-CA-PROJ-RVW-B"),--(B2:B1845,"44"))

i'm values in range A2:A1845 & B2:B1845.

 

[RoryA]

You're missing two = signs:

=SUMPRODUCT(--(A2:A1845="WR-CA-PROJ-RVW-B"),--(B2:B1845="44"))

 

[karthi5565]

You're missing two = signs:

=SUMPRODUCT(--(A2:A1845="WR-CA-PROJ-RVW-B"),--(B2:B1845="44"))

Wow...... Thanks a million... now it works... awesome... mmuuuuah....

 

[Belli]

Hi. I am having the same problem. Please help.

The following is the formula in Excel 2010 version. What it should be in Excel 2003 version?

=COUNTIFS(BC33:BC232, "=this month",J33:J232,"=Connect")