Count using Multiple Criteria

D

Dennis

I have a spered sheet I am using to keep track of leaks of different types
and it covers several years. I would like a tally sheet by year. This
requires that I use the year column and the leak type column in my search
criteria.


Date Unit Type Of Leak Location
12/00/02 1811 Slab Hot water line to washing machine
11/00/02 1515 Grout Grout around bath tub
11/00/02 16th Stack Pin hole Main shutoff leaking
10/00/02 Pump Room Pin hole 2 1/2 supply line to pump
10/00/02 109 Plumbing Patio hosebib supply
9/00/02 10th Floor Plumbing Across from the South Planter
8/00/02 1216 Plumbing repairs

I have made several attempts at a formula and none work
I found an article in www.officearticals.com but it does not work

{=SUM((Sheet1!A6:A36="06")*(Sheet1!C6:C36="slab"))}
Am I missing something?
 
I

Ian G

I normally create a separate column on the main list to do tasks like
this. In column e, enter the formula =Year(a2)&c2 which should give a
result of '2002Slab.' Copy this formula down to the bottom of your
list.

Then on a new tab create a table with years along the top (row 1)
(2002, 2003 etc) and Types of leak down the side (column A) (Slab,
Grout etc.) Go to cell B2 and enter the formula =countif(<Main Sheet
column E>,b$1&$a2) Copy this formula to all the cells in the table, and
you should have the numbers you need.

Assuming the main sheet is 'Sheet1' the formula will be
=COUNTIF(Sheet1!E:E,B$1&$A2)

Ian G
 
D

daddylonglegs

IF column A contains dates then you need to use

{=SUM((YEAR(Sheet1!A6:A36)=2006)*(Sheet1!C6:C36="slab"))}

or you could use SUMPRODUCT which doesn't require CTRL+SHIFT+ENTER

=SUMPRODUCT((YEAR(Sheet1!A6:A36)=2006)*(Sheet1!C6:C36="slab")
 
P

Pete_UK

The formula you quote is an array formula - you should omit the curly
braces when you type it in and then instead of pressing <enter> to
comit it, you should do <CTRL><SHIFT><ENTER> all at the same time
(referred to as CSE). If you do it right, Excel will wrap the curly
braces around the formula for you.

Anyway, I think the formula should be:

=SUM(IF((YEAR(Sheet1!A6:A36)=2006)*(Sheet1!C6:C36="slab"),1,0))

This will count how many "slab" type leaks you have in 2006 if that is
what you want. Don't forget to comit it with CSE, and everytime you
edit it.

Hope this helps.

Pete
 
K

Ken Wright

You might also want to consider a Pivot Table, as it was made to analyse
data like this. If you fancy trying then here is an intro:-

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

Also, for a good explanation of the SUMPRODUCT function you have been
given:-

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :)
------------------------------­------------------------------­----------------
 
Top