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

 

[graceyentw]

Hi,

I use COUNTIFS function as following at excel2007, works correctly.
=COUNTIFS(M3:M2136,2, C3:C2136,L2139)
Then I convert it for excel2003 as following
=SUMPRODUCT(--(M3:M2136=2),--(C3:C2136=L2139))

The SUMPRODUCT function doesn't count right.
Is it the range too big? I tested them both. They both have same value until reach the row 1907 (e.g. M3:M1907). After row 1907, the SUMPRODUCT function can't count, even it meets the criteria.
Any suggestion to get what I need for excel 2003?
Thanks.

 

[shg]

Are the values you're searching for numeric, and some of the values in the columns numbers stored as text (which will be immediately apparent if you lave Excel's default alignment alone)?

COUNTIF will ignore the distinction, SUMPRODUCT won't.

 

[graceyentw]

I set format cells of the column to both general and number. None of them work.

 

[graceyentw]

Hi Shg,

Thanks, you are right. After checked cell by cell, I did see them align on different side even though I set them all to numbers. I got them all right now. Thanks again.

 

[GoKats78]

I'm having the same problem...here is the "countifs" statement...what is the corresponding "sumproduct" statement?

=IF(AND(E10="",A10=""),"",IF(_xlfn.COUNTIFS($C$2:$C10,C10,$D$2:$D10,D10)=1,"Original Entry","CHANGED!!"))

 

[RoryA]

=IF(AND(E10="",A10=""),"",IF(SUMPRODUCT(($C$2:$C10=C10)*($D$2:$D10=D10))=1,"Original Entry","CHANGED!!"))

 

[GoKats78]

That's it! I was ALMOST there when you provided the answer! It could have been the next thing I tried...or 5 or 6 hours of trying until I got there!

 

[JayArrow]

Hi

I am having a very similar problem with moving over to 2003 from 2007. Again its a work thing haha.

The formula I am trying to work with is: =COUNTIFS(Stats!B4:B15,"<30",Stats!B4:B15,">0.01")

Can anyone point me in the right direction? Will a SUMPRODUCT function work for me?

Thanks in advance

 

[barry houdini]

Hello JayArrow, welcome to MrExcel,

Yes you could use SUMPRODUCT, e.g.

=SUMPRODUCT((Stats!B4:B15< 30)*(Stats!B4:B15>0.01))

or use two COUNTIF functions, one subtracted from the other like this

=COUNTIF(Stats!B4:B15,"<30")-COUNTIF(Stats!B4:B15,"<=0.01")

[so the 2nd COUNTIFS has the > reversed to <= because you are subtracting that count]

 

[JayArrow]

Hello JayArrow, welcome to MrExcel,

Yes you could use SUMPRODUCT, e.g.

=SUMPRODUCT((Stats!B4:B15< 30)*(Stats!B4:B15>0.01))

or use two COUNTIF functions, one subtracted from the other like this

=COUNTIF(Stats!B4:B15,"<30")-COUNTIF(Stats!B4:B15,"<=0.01")

[so the 2nd COUNTIFS has the > reversed to <= because you are subtracting that count]

Thanks for the warm welcome and thanks for your quick response. It works a treat

That had been frustrating me for almost 24 hours and you fixed it in minutes. Much appreciated