Count cells with multiple criteria

  • Thread starter Thread starter GTS
  • Start date Start date
G

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
 
GTS said:
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
Click to expand...

=SUMPRODUCT((A1:A100="USA")*(B1:B100="A")*(LEFT(C1:C100,3)="ATR"))
 
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
 
use the CountIf function, setup cells for each count then
total, or user CountIf with the , and/or function.
 
use the CountIf function, setup cells for each count then
total, or user CountIf with the , and/or function.
Click to expand...

Read the other responses. Your suggestion, limited and devoid of solid examples
as it is, won't work.
 
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





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






**
 
Back
Top