# Count based on 2 cell values

#### amartinez

##### New Member
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.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### dafan

##### Well-known Member
Code:
``=SUMPRODUCT((owner="john")*(status="Cancelled"))``

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

#### amartinez

##### New Member
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

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

Code:
``=SUMPRODUCT((owner="Frank")*(status="Cancelled"))``

#### Yogi Anand

##### MrExcel MVP
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
ABCDEFGH
1OwnerStatusCount of StatusStatus
2johnActiveOwnerActiveCanceledCancelledGrand Total
3johnCanceledAl11
4johnCancelledFrank11
5johnActivejohn2114
6AlActiveGrand Total4116
7FrankActive
8
Sheet7

#### amartinez

##### New Member
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

##### MrExcel MVP
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

##### New Member
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.

Replies
14
Views
169
Replies
4
Views
131
Replies
0
Views
269
Replies
9
Views
280
Replies
4
Views
260

1,191,183
Messages
5,985,172
Members
439,944
Latest member
Vangelis74

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back