# COUNTIFS formula convert to 2003

1. ## COUNTIFS formula convert to 2003

Please would someone be kind enough to help me convert this formula so that people using 2003 can see the data:

=COUNTIFS('Sales'!\$A\$4:\$A\$400,"JAN",'Sales'!\$V\$4:\$V\$400,1)

Would really appreciate any help at all.
Thank you!!!   Register To Reply

2. ## Re: COUNTIFS formula convert to 2003

Try:

=SUMPRODUCT(('Sales'!\$A\$4:\$A\$400="JAN")*('Sales'!\$V\$4:\$V\$400=1))  Register To Reply

3. ## Re: COUNTIFS formula convert to 2003

Just tried it and it has come back with an error #####VALUE  Register To Reply

4. ## Re: COUNTIFS formula convert to 2003

Is the column wide enough to display the result?  Register To Reply

5. ## Re: COUNTIFS formula convert to 2003

yes, I have just checked and I have also expanded it further just incase.  Register To Reply

6. ## Re: COUNTIFS formula convert to 2003

So are you getting ######### or are you getting #VALUE! error?

If the Countifs worked, so should the Sumproduct.  Register To Reply

7. ## Re: COUNTIFS formula convert to 2003

Apologies it is a #VALUE!  Register To Reply

8. ## Re: COUNTIFS formula convert to 2003

So then I think you must have that error in one of the 2 lookup columns.... it's needs to be fixed.  Register To Reply

9. ## Re: COUNTIFS formula convert to 2003

Can you attach your workbook? My guess is that your numbers are probably stored as text, but I'm not really sure if that would affect SUMPRODUCT.  Register To Reply

10. ## Re: COUNTIFS formula convert to 2003

Text entries won't affect this sumproduct formula.  Register To Reply

11. ## Re: COUNTIFS formula convert to 2003

Do you have a comparison statement in all parts of the sumproduct? If so, the formula should work, unless your ranges are different in size. The * operator in Sumproduct will throw an error if one of the component of the sumproducts returns text. A formula like

=SUMPRODUCT(('Sales'!\$A\$4:\$A\$400="JAN")*'Sales'!\$V\$4:\$V\$400)

will throw an error if column V contains text. In that case, use the -- version, like

=SUMPRODUCT(--('Sales'!\$A\$4:\$A\$400="JAN"),'Sales'!\$V\$4:\$V\$400)

For a discussion of Sumproduct and its error messages, see here http://spreadsheet-toolbox.com/libra...error-message/  Register To Reply

12. ## Re: COUNTIFS formula convert to 2003

properly laid out data would enable a Pivottable to do this  Register To Reply

13. ## Re: COUNTIFS formula convert to 2003

Did you resolve this?

COUNTIFS will ignore errors in the ranges but the suggested SUMPRODUCT and other formulas won't ignore those errors. It would be best to eliminate any errors in the referenced ranges but if you don't this formula might work for you

=SUMPRODUCT(ISNUMBER(MATCH(Sales!\$A\$4:\$A\$400,{"JAN"},0))*ISNUMBER(MATCH(Sales!\$V\$4:\$V\$400&"",{"1"},0)))

That will ignore errors as COUNTIFS does and also it will count both numeric 1s and text "1"s as COUNTIFS does  Register To Reply