convert countifs to sumproduct formula

[cgreen]

I have a formula being used in ms2007
=COUNTIFS(ALL_OPEN!M:M,"",ALL_OPEN!Q:Q,"<=14"). The answer I receive is 1007, which is the correct answer.

Countifs does not work in ms2003; therefore, I am trying to use
=SUMPRODUCT(--(ALL_OPEN!$M$2:$M$10000=""),--(ALL_OPEN!$Q$2:$Q$10000<=14)) in its place. The answer I receive for the sumproduct formula is 3718, which is wrong.

What am I doing wrong???

 

[barry houdini]

Unlike COUNTIFS the SUMPRODUCT formula will identify any blanks in column Q as zero, so that formula will count completely blank rows, try adding another condition to check that Q isn't blank, i.e.

=SUMPRODUCT(--(ALL_OPEN!$M$2:$M$10000=""),--(ALL_OPEN!$Q$2:$Q$10000<=14),--(ALL_OPEN!$Q$2:$Q$10000<>""))

 

[Andrew Poulsom]

Your SUMPRODUCT formula looks right to me. Can you get different results with a smaller range? If so please post what's in coluns M and Q.

 

[cgreen]

If I have something other than blank data in column M, then everything works greats .. per this example:
=SUMPRODUCT(--(ALL_OPEN!$M$2:$M$10000=TEXT($A5,0)),--(ALL_OPEN!$Q$2:$Q$10000<=14))

When I try to get a count of blank cells I have a problem.

column M contains text data like 261, 201, or a blank cell with no spaces. Column Q contains numbers bysubtracting a date from the date the data was created. Such as 1, 10, 14, 265, etc to symbolise a day count.

Does that answer your question?

 

[Andrew Poulsom]

Did you see Barry's reply?

 

[cgreen]

Yes, after I posted my reply ..... works great. The talent of this board amazes me every time.

Thank you