# 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!!!

2. ## Re: COUNTIFS formula convert to 2003

Try:

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

3. ## Re: COUNTIFS formula convert to 2003

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

4. ## Re: COUNTIFS formula convert to 2003

Is the column wide enough to display the result?

5. ## Re: COUNTIFS formula convert to 2003

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

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.

7. ## Re: COUNTIFS formula convert to 2003

Apologies it is a #VALUE!

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.

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.

10. ## Re: COUNTIFS formula convert to 2003

Text entries won't affect this sumproduct formula.

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/

12. ## Re: COUNTIFS formula convert to 2003

properly laid out data would enable a Pivottable to do this

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1