COUNTIF almost does it but...

[Stevej]

...I need a 'conditioned' countif. I have 2 columns, a
department and a status. I want to have a summary that
has;

Dept total status a count status b count status c...

If I do a coutif I can get the count easily enouth with a
=COUNTIF(Sheet1!$L$2:$L$1500,E2) where e2 is the
department name and the sheet1!l2:l1500 is the data.

the tricky part you ask? I want a countif conditional
for the status lines. ie if department is (e2) and
status is "a" countif same again for status b, c, d...
so it would look like this

Dept total status a count status b count status c
1 12 4 5 7
2 24 24 0 0

The total column is a straight countif, works fine.
How do I get the status from a single column on sheet 1
counted to summary on sheet 2?

 

[Frank Kabel]

Hi
use
=SUMPRODUCT(--(Sheet1!$L$2:$L$1500=E2),--(Sheet1!$M$2:$M$1500="a"))

if column M stores the status. Of yourse you can replace the hardcoded
'a' with a cell reference. For more about Sumproduct see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Stevej]

beautiful..thanks muchly

-----Original Message-----
Hi
use
=SUMPRODUCT(--(Sheet1!$L$2:$L$1500=E2),--(Sheet1! $M$2:$M$1500="a"))

if column M stores the status. Of yourse you can replace the hardcoded
'a' with a cell reference. For more about Sumproduct see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany



.