# Thread: COUNTIF or SUMIF (Excel 2000 SR1)

1. ## COUNTIF or SUMIF (Excel 2000 SR1)

I need to use something like COUNTIF or SUMIF where there is more than one criteria. For example, I only want the cell counted if the record contains a certain country (all countries are in one column) AND if the record is for a certain product (all products are in one column in the database)...Rory mentioned using array formulas but I can't get them to work....I would like to add all relevant results into one cell.

Christa
<img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

2. ## Re: COUNTIF or SUMIF (Excel 2000 SR1)

Hi Christa,
If you had countries in A1:A6 and products in B1:B6 and you wanted to find how many times USA and Coke were in the same row, you would type:
=SUM((A1:A6="USA")*(B1:B6="Coke"))
and then press Ctrl+Shift+Enter to array-enter it.
Hope that helps.

3. ## Re: COUNTIF or SUMIF (Excel 2000 SR1)

If you are still having trouble, try the 'Conditional Sum Wizard' under Tools|Add ins. It should be able to create the array formula for you.

Cheers,
Paul

4. ## Re: COUNTIF or SUMIF (Excel 2000 SR1)

Thanks, Rory...that worked. Now I have a related question...I need a similar sort of formula that would essentially use a Sumif formula only it has to look at 2 criteria. For example, I want it to look at a range called country and pick out "Canada", look at a range called "Version" and pick out "Accpac" and then sum all the cells in a range called Quantity, that meet those criteria.

Any ideas?

Thanks,

Christa

5. ## Re: COUNTIF or SUMIF (Excel 2000 SR1)

Try the array formula, {=SUM(IF(Country="Canada",IF(Version="Accpac",Quan tity,""),""))} where the braces are entered by Excel when you press Ctrl+Shift+Enter. --Sam

6. ## Re: COUNTIF or SUMIF (Excel 2000 SR1)

Try this Array formula: