Countifs alternative for Excel 2003 / sumproduct help

[Merellia]

Hi! I'm using Excel 2003 (old, I know, but it has suited my very limited needs until today).

I'm trying to count the instances of X that also meet Y criteria. Both X and Y are text, in different columns of the same row. In post-2003 I would use countifs, which doesn't exist in 2003. I believe the alternative is sumproduct, but I haven't been able to get it to work either.

I *think* the countifs language would be =countifs(readers!f2:f999,"M",readers!g2:g999,"Yes")

Using sumproduct, I have tried:


=sumproduct(--(ISTEXT(readers!f2:f999="M")),--(ISTEXT(readers!g2:g99="Yes")))
This returns a value error. I've tried variations of this too (without ISTEXT, without the dashes) to no avail.

Any help on how to get a working sumproduct formula would be much appreciated!

 

[Ingolf]

Maybe,

=SUMPRODUCT(--(F2:F14="M"))+SUMPRODUCT(--(G2:G14="yes"))

 

[jtakw]

Hi,

Try this:

Excel 2010
	F	G
2	M
3	M	Yes
4	M	Yes
5	M	Yes
readers

Excel 2010
	B
1	3
2
Sheet2

Cell Formulas
Range	Formula
B1	=SUMPRODUCT((readers!F2:F999="M")*(readers!G2:G999="Yes"))

 

[Snakehips]

Merellia,

Try...
=SUMPRODUCT((F2:F999="M")*(G2:G999="Yes"))

The * operator within the Sumproduct effectively converts True & False to 1 & 0 so the double unary, -- ,is unnecessary.
* is also AND whereas + is OR
Hope that helps.

The

 

[Merellia]

Thank you all! I've got it working based on your suggestions--I'm so pleased. THANK YOU!

 

[jtakw]

You're welcome, welcome to the forum.