Countifs function equivalent for Excel 2002 (XP)

[workme]

Hi I am hoping that someone can help me out, I need Countifs function equivalent for Excel 2002 (XP)... But I also need it to work with counting names, as I have tired to use the sumproduct function but it only like to work with number, I need one that works with names and multiple names at that.. I have a spreadsheet that I need to count how many times two different names are used on said spreadsheet.. I.E. Company 1 + trucks-in = ? Please and thank you

 

[RoryA]

SUMPRODUCT will count anything, not just numbers. What is the data layout and how did you use it?

 

[T. Valko]

Hi I am hoping that someone can help me out, I need Countifs function equivalent for Excel 2002 (XP)... But I also need it to work with counting names, as I have tired to use the sumproduct function but it only like to work with number, I need one that works with names and multiple names at that.. I have a spreadsheet that I need to count how many times two different names are used on said spreadsheet.. I.E. Company 1 + trucks-in = ? Please and thank you

If you have a COUNTIFS formula post it and we'll be able to translate it into a formula that can be used in every version of Excel.

 

[workme]

SUMPRODUCT will count anything, not just numbers. What is the data layout and how did you use it?

Hi Rory,

Thanks for the quick responses,,,

I tried using the function SUMPRODUCT and it did not work I just get #VALUE!

This is my formula I used =SUM((H13:H215="Dollco Printing")*(W12:W215="confirmed"))...

I need to calculate how many times I use the Dollco Printing and confirmed on my spreadsheet.... at home I used COUNTIFS function and it worked fine but it is not supported by excel 2002...

Let me know if this is enough information.

Thanks again.

Jim

 

[RoryA]

That's not a SUMPRODUCT formula, nor is it complete, which makes it pretty hard to troubleshoot. I second Biff's suggestion - post the COUNTIFS version for translation.

 

[workme]

Sorry guys new to this excel thing,, I was trying another formula and posted it by mistake,,,, here is the COUNTIFS one that I used with excel 2007... Or the way it was translated by excel 2002...

=_xlfn.COUNTIFS($H$13:$H$215,"Econopac",$W$13:$W$215,"confirmed")

Thanks

Jim

 

[RoryA]

=SUMPRODUCT(($H$13:$H$215="Econopac")*($W$13:$W$215="confirmed"))

should work.

 

[workme]

Thanks Rory, I'll give it a try in a bit and let you know.

Jim

 

[T. Valko]

Sorry guys new to this excel thing,, I was trying another formula and posted it by mistake,,,, here is the COUNTIFS one that I used with excel 2007... Or the way it was translated by excel 2002...

=_xlfn.COUNTIFS($H$13:$H$215,"Econopac",$W$13:$W$215,"confirmed")

Thanks

Jim

Try it like this...

=SUMPRODUCT(--($H$13:$H$215="Econopac"),--($W$13:$W$215="confirmed"))

Or, using cells to hold the criteria:

• A1 = Econopac
• B1 = confirmed

=SUMPRODUCT(--($H$13:$H$215=A1),--($W$13:$W$215=B1))

 

[workme]

Hi Roy,

Thanks your formula worked,,, it's much appreciated...

Jim