Count based on 2 cell values

[amartinez]

I have colum b2:b113 ( named cell range as owner) and colum c2:c113 (named cell range as status), I have to add data based on the name of the owner and the under the status of the owner.
e.g.

Owner Status
john Active
john Canceled
john Cancelled
john Active
Al Active
Frank Active

I am using the formula =sumproduct((owner="john")*(status="Cancelled"))

but it doesn't seem to be working. I have also tried it without the named range and just entering the cell range manually b2:b113 and still it doesn't work. Can some one please help me figure out another way to do this.

 

[dafan]

=SUMPRODUCT((owner="john")*(status="Cancelled"))

Works fine for me...gives answer = 2.

 

[amartinez]

I have a list of about 7 names, and another of about 6 different statuses.

I need excel to go over colum b2:b113 and look for a specific name and count how many times the name is found as long as the name matches a specific status.

find " Frank ' colum b1:b113 with status of "Canceled" colum c1:c113 , then return the number of times Frank has the status Canceled.

I doesn't return the correct number of status.

 

[dafan]

I assume you have named B2:B113 "owner" and C2:C113 named "status", NOT the list with names and status?

=SUMPRODUCT((owner="Frank")*(status="Cancelled"))

 

[Yogi Anand]

I have colum b2:b113 ( named cell range as owner) and colum c2:c113 (named cell range as status), I have to add data based on the name of the owner and the under the status of the owner.
e.g.

Owner Status
john Active
john Canceled
john Cancelled
john Active
Al Active
Frank Active

I am using the formula =sumproduct((owner="john")*(status="Cancelled"))

but it doesn't seem to be working. I have also tried it without the named range and just entering the cell range manually b2:b113 and still it doesn't work. Can some one please help me figure out another way to do this.

Hi amartinez:

Welcome to MrExcel Board!

How about using Pivot Table as illustrated in the following:

Book1
	A	B	C	D	E	F	G	H
1	Owner	Status		Count of Status	Status
2	john	Active		Owner	Active	Canceled	Cancelled	Grand Total
3	john	Canceled		Al	1			1
4	john	Cancelled		Frank	1			1
5	john	Active		john	2	1	1	4
6	Al	Active		Grand Total	4	1	1	6
7	Frank	Active
8
Sheet7

 

[amartinez]

Thanks for your reply. I'm not sure I understand the pivot table you are refering to.

This list is for items assigned to employees and based on the date we select the status from a drop down menu. There actually are more statuses and they are based on completion date.

I have statuses ;

Completed/On Time
In Progress/On Time
Completed/Delayed
In Progress/Delayed
In Review
Canceled
Rejected
Postponed

It is a list we work on based on items worked and when they are completed. These items are worked withing up to 2 months time. The list has 112 items and each has its status and owner name. The range with the names is the range I named owner and the range with tne status is the one named status.

 

[Yogi Anand]

Hi amartinez:

Refering to my post, I have reproduced your data in cells A1:B7

Based on the database cell entries A1:B7, Pivot Table is produced in cells D1:H7 using Owner in Row, Status in Column as Count of Status in Data.

I hope this helps.

 

[amartinez]

Thanks a lot. It sure looks like what I'm trying to get, it just still seems to be a bit too advanced for what I can do yet. I will keep trying till I do the pivot table. Thanks for the info.