COUNTIF or SUMIF (Excel 2000 SR1)

Topic

New Reply

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.

Thanks in advance

Christa

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Try this Array formula:

=SUM((Country="Canada")*(Version="Accpac")*(C1:C5))

Reply | Quote

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

Reply | Quote