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

 

[VoG]

You can use SUMPRODUCT e.g.

=SUMPRODUCT(--(H1:H100="rbs"),--(I1:I100<4))

Please note that you can't use whole column references like H:H.

 

[Richard Schollar]

Hi

Use Sumproduct instead:

=SUMPRODUCT(--(H2:H100="rbs"),--(I2:I100< 4))

Note that you can't use whole column refs with sumproduct (so no H:H). Search the board for Sumproduct - you will find literally thousands of examples.

 

[Time-Out]

Hi,

I didn't think it worth creating a new thread as I have pretty much the same issue. I'm trying to get my table to work with Xcelsius, however, Xcelsius doesn't read COUNTIFS. Being rather new to all this excel stuff, I have NO IDEA what formula to use in its place.

My current formula for a column (in vba) is: =COUNTIFS(Sheet3!C[-1],RC[-1],Sheet3!C[+3],R1C2)

Would SUMPRODUCT work for this? It's basically seeing how many court cases were created per court per month from raw data extracted from the Court Management System.

 

[GlennUK]

SUMPRODUCT appears to be supported by Xcelsius, so yes, except do not use entire column references.

 

[Time-Out]

Thanks, what does "--" mean, please?

 

[VoG]

For example (H1:H100="rbs") returns an array of False/True values. -- coerces that into an array of 0/1 that SUMPRODUCT can process.

 

[MTMann122]

I am having a similar problem with a formula I created in Excel 2007. When I open the spreadsheet on a computer running 2003 I get the "NAME"! returned in the cells with the following formula. Any help is appreciated.

If the value in cells B3:B87 = "ENQ1" and the value of cells H3:H87 = 1, then count values.

=COUNTIFS($B$3:$B$87, "ENQ1",H$3:H$87,1)

 

[VoG]

Try

=SUMPRODUCT(--($B$3:$B$87 = "ENQ1"),--(H$3:H$87=1))

 

[MTMann122]

Thank you VoG!!!! It worked perfectly.