Count cells with multiple criteria

[GTS]

Hello, suppose i have a worksheet like this:

Column A Column B Column C
Type A Type B Type C

USA A ATR-0
USA P ATH-6
UK A KAL-8
UK P VEN-4
SPAIN P ATH-4
SPAIN A FIL-9
USA A ATR-8

Ok now, i'd like to count the number of rows that satisfy the criteria
Column A is "USA" AND column B is "A" and column C is begging with AT
(that is "ATR*" i think)

What is the function that does that? The result is 2 in the exampl
above.

Thanx,
Georg

 

[Paul]

Hello, suppose i have a worksheet like this:

Column A Column B Column C
Type A Type B Type C

USA A ATR-0
USA P ATH-6
UK A KAL-8
UK P VEN-4
SPAIN P ATH-4
SPAIN A FIL-9
USA A ATR-8

Ok now, i'd like to count the number of rows that satisfy the criteria:
Column A is "USA" AND column B is "A" and column C is begging with ATR
(that is "ATR*" i think)

What is the function that does that? The result is 2 in the example
above.

Thanx,
George

=SUMPRODUCT((A1:A100="USA")*(B1:B100="A")*(LEFT(C1:C100,3)="ATR"))

 

[Peo Sjoblom]

One way

=SUMPRODUCT(--(A3:A10="USA"),--(B3:B10="A"),--(LEFT(C3:C10,3)="ATR"))

or perhaps better usability

=SUMPRODUCT(--(A3:A10=E3),--(B3:B10=F3),--(LEFT(C3:C10,3)=G3))

where the cells hold the criteria, that way it is easier to change the
criteria without editing the formula itself

 

[kathy]

use the CountIf function, setup cells for each count then
total, or user CountIf with the , and/or function.

 

[Harlan Grove]

use the CountIf function, setup cells for each count then
total, or user CountIf with the , and/or function.

Read the other responses. Your suggestion, limited and devoid of solid examples
as it is, won't work.

 

[Jon Barchenger[MS]]

Good afternoon George -

See if this formula will work for you:

=SUM(IF((A2:A8="USA")*(B2:B8="A")*(LEFT(C2:C8,SEARCH("-",C2:C8)-1)="ATR"),1,0))

Make sure that you enter it using CTL+SHIFT+ENTER - this will enter it as an array formula.

Let me know if you need more info or help.
Thanks,
Jon Barchenger



--------------------
**From: GTS <[email protected]>
**Subject: Count cells with multiple criteria
**Date: Thu, 13 Nov 2003 06:49:16 -0600
**Message-ID: <[email protected]>
**Organization: ExcelTip
**User-Agent: ExcelTipForum
**X-Newsreader: ExcelTipForum
**X-Originating-IP: 195.170.19.2
**Newsgroups: microsoft.public.excel.worksheet.functions
**NNTP-Posting-Host: 69-56-172-122.theplanet.com 69.56.172.122
**Lines: 1
**Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
**Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.worksheet.functions:171001
**X-Tomcat-NG: microsoft.public.excel.worksheet.functions
**
*

Hello, suppose i have a worksheet like this:
**
**Column A Column B Column C
**Type A Type B Type C
**
**USA A ATR-0
**USA P ATH-6
**UK A KAL-8
**UK P VEN-4
**SPAIN P ATH-4
**SPAIN A FIL-9
**USA A ATR-8
**
**Ok now, i'd like to count the number of rows that satisfy the criteria

Column A is "USA" AND column B is "A" and column C is begging with AT

(that is "ATR*" i think)
**
**What is the function that does that? The result is 2 in the exampl

above.
**
**Thanx,
**Georg





-----------------------------------------------






**