# Solved: EXCEL 97 "=countifs" alternative needed



## the-san (Apr 6, 2007)

Hi all,

Ive got to do some statistical analasis for work, I need to search 2 columns for 2 different types of data and collate.

For example search for "a system" in A:A & "a reason" in F:F which matches the critera fount in A:A

I have office 2007 at home which was fine because I just used the "=COUNTIFS" formula and it worked fine, but I then saved to 97 format because thats what they use in work and the "=COUNTIFS" formula is not back compatible.

Is there any way I can do this with 97, any help would be greatly appreciated.


----------



## bomb #21 (Jul 1, 2005)

Search Google groups for "COUNTIF multiple criteria"; or see http://www.j-walk.com/ss/excel/tips/tip74.htm

HTH.


----------



## the-san (Apr 6, 2007)

thank you very much !


----------



## bomb #21 (Jul 1, 2005)

Forgot to say welcome to TSG. 

Use Thread Tools at the top of the page to Mark Solved if you wish.


----------



## the-san (Apr 6, 2007)

It didnt work unfortunatly comes up with #value
i tried doing the example on the page you listed and that didnt work either.
Gonna keep looking.


----------



## the-san (Apr 6, 2007)

I gave up, in the end I used a pivot table.


----------



## bomb #21 (Jul 1, 2005)

the-san said:


> It didnt work unfortunatly comes up with #value
> i tried doing the example on the page you listed and that didnt work either.
> Gonna keep looking.


Sorry, they ought to work.  I'll post specific examples in case anyone searches for the same thing.

In A1:A3 -- "Fish", "Fish", "Jaffa"
In B1:B3 -- "Cakes", "Fingers", "Cakes"

From John W's page, use the "Count of Sales where Month='Jan' AND Region='North'" example as follows:

=SUM((A1:A10="Fish")*(B1:B10="Fingers"))

NB: this is an _array_ formula. Use *Ctrl+Shift+Enter* to enter it, not plain *Enter*. Do it right & it'll appear in the formula bar with curly brackets,

{=SUM((A1:A10="Fish")*(B1:B10="Fingers"))}

The curly brackets _must_ be created using *Ctrl+Shift+Enter* -- just typing them won't work.

Searching Google Groups should yield hits for SUMPRODUCT, a popular non-array alternative. A SUMPRODUCT version would be:

=SUMPRODUCT((A1:A10="Fish")*(B1:B10="Fingers"))

Both formulas should return 1 (A2 = "Fish", B2 = "Fingers").


----------



## the-san (Apr 6, 2007)

The weird thing is the formula ({=SUM((A1:A10="Fish")*(B1:B10="Fingers"))}) works with excel 2007, but comes up with #value in excel 2003. I havent tried it with excel 97 yet because I havent been in work to try the spreadsheet, but im assuming its going to do the same thing.

Still havent found a way to do it with a formula, saying that its easter weekend so Ive thrown caution to the wind and had a mad weekend instead.


----------

