COUNTIFS 2003 - Impossible to convert

[ADAMD30]

I have been trying to convert the following countifs formula to 2003 and have tried all possibilities listed on this website from what I can, Specifically using sumproduct instead.

Can any one help, I am at a complete loss!!!!!

The formual is written on an overview sheet, pulling out information from a shreadsheet detailing lists of company credits.

Essentially listing how many credits an account has had, between specific dates and for a specific reason.

= COUNTIFS(Credits!$B$2:$B$257,">="&$C$4,Credits!$B$2:$B$257,"<="&$E$4,Credits!$D$2:$D$257,B73,Credits!$H$2:$H$257,$E$26)

,">="&$C$4 - refers to a date criteria
,"<="&$E$4 - refers to a date criteria

B73 - refers to along account name

$E$26 - refers to complaint type

Please anyone let me know your thoughts?

[Whizbang]

=SUMPRODUCT(--(Credits!$B$2:$B$257>=$C$4),--(Credits!$B$2:$B$257<=$E$4),--(Credits!$D$2:$D$257=B73),--(Credits!$H$2:$H$257=$E$26))

[Domski]

Maybe:

=SUMPRODUCT(--(Credits!$B$2:$B$257>=$C$4),--(Credits!$B$2:$B$257<=$E$4),--(Credits!$D$2:$D$257=B73),--(Credits!$H$2:$H$257=$E$26))

Dom

[ADAMD30]

Thanks Domski and Whizbang - unfortunately these don't work, I agree I thought it should.

B73 refers to a 20 letter long account name that countifs was fine with searching on, but would sumproduct have a problem with it?

Any other ideas guys?

[Domski]

Did you try both of them? They were slightly different until Whizbang removed the &'s from his. I don't think the length of the account name is any problem.

Dom

[ADAMD30]

Yes tried both, is there another way around this conversion?

[Domski]

I don't think it should matter but you could try:

=SUMPRODUCT((Credits!$B$2:$B$257>=$C$4)*(Credits!$B$2:$B$257<=$E$4)*(Credits!$D$2:$D$257=B73)*(Credits!$H$2:$H$257=$E$26))

Dom

[Whizbang]

Here is an example workbook using your formula.


*** Edit***

Oops. Forgot to put the workbook in 2003 format.