Countifs error in earlier version of excel

[bfb314159265]

I am trying to use this formula:

COUNTIFS('GAME DATA'!$B$7:$B$361,"=W",'GAME DATA'!V$7:V$361,"5")

This is an attempt to count the number of times a row has both a "W" in one column and the number 5 in another column. However, in my version of excel it just says "#NAME?". Is there an alternative way to do this formula?

Thanks for the help.

 

[Jonmo1]

Welcome to the board..

Yep, Countifs and Sumifs did not exist until XL2007.

Try

=SUMPRODUCT(--('GAME DATA'!$B$7:$B$361="W"),--('GAME DATA'!V$7:V$361="5"))

 

[Ron Coderre]

The COUNTIFS() function is only part of Excel 2007 (and later versions)

Try something like this:

=SUMPRODUCT(('GAME DATA'!$B$7:$B$361="W")*('GAME DATA'!$7:V$361="5"))

Does that help?

 

[bfb314159265]

Thanks for the help.

That gets rid of the error message but gives me 0 for every cell...

 

[Jonmo1]

Well, both our responses were based on the assumtion that the formula you posted works in xl2007(or higher)...

But the most obvious reason for that to return 0, is the quotes around the number 5
But again, assuming your posted formula works in 2007 or higher, then the posted resolutions should work as well...

But anyway, try without those quotes

=SUMPRODUCT(--('GAME DATA'!$B$7:$B$361="W"),--('GAME DATA'!V$7:V$361=5))

 

[Ron Coderre]

It sounds like Col_V contains numbers, not text...and there's an error in the reference...try this:

=SUMPRODUCT(('GAME DATA'!$B$7:$B$361="W")*('GAME DATA'!$V$7:V$361=5))

Does that solve the problem?

 

[bfb314159265]

That fixed it.

Thanks!

 

[DaveOD]

It sounds like Col_V contains numbers, not text...and there's an error in the reference...try this:

=SUMPRODUCT(('GAME DATA'!$B$7:$B$361="W")*('GAME DATA'!$V$7:V$361=5))

Does that solve the problem?

Hi, I've had the same issue as this but haven't been able to translate the formula via the SUMPRODUCT method.

Here's what I'm trying to do:

=COUNTIFS('Datasheet Jan 13 - May 13'!P:P,"<"&3,'Datasheet Jan 13 - May 13'!U:U,"AA1234")

I know I'm probably doing something silly but have no idea what it is ... any suggestions are greatly appreciated.

Many thanks,

Dave

 

[Jonmo1]

Welcome to the board.

With the assumption that the formula posted works as desired in XL2007+, then an equivelent using sumproduct would be

=SUMPRODUCT(--('Datasheet Jan 13 - May 13'!P$1:P$1000<=3),--('Datasheet Jan 13 - May 13'!U$1:U$1000="AA1234"))

Note, you can't use entire column references like P:P in Sumproduct/XL2003.
This may be what was hanging you up.

 

[DaveOD]

Hi Jonmo1,

Thanks for the welcome and even more so for the formula: it works a treat!