COUNTing....with conditions.

mmlugar

New Member
Joined
Sep 2, 2002
Messages
3
Here’s the situation:
I have three columns on the spreadsheet.
Column 1 = This has the initials of the analyst who is assigned responsibility for overseeing a report. (i.e. JJC is responsible for Report GK, JLL is responsible for Report LLR, etc)
Column 2 = The Intern (not the analyst) inserts his/her initials here as soon as the report enters our office. Otherwise, this cell(s) is blank.
Column 3 = The Analyst inserts his/her initials here as soon as he/she enters the report in the database. Otherwise, this cell(s) is blank.

Here’s what I need:

How many reports have entered our office by analyst (i.e. how many reports assigned to JJC have entered the office?).

How many reports assigned to said analyst have been entered into the database?

Eventually I will need these broken down into %, but that should be easy.

This is definitely a counting function issue, but I haven’t been able to get DCOUNTA or COUNTIF to do anything. Your help is much appreciated!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

maxflia10

Well-known Member
Joined
May 20, 2002
Messages
890
Matt,

Use SUMPRODUCT

=sumproduct(range,criteria)*(range,criteria)

both ranges must be the same size
 

mmlugar

New Member
Joined
Sep 2, 2002
Messages
3
Not quite what I am looking for. Let me be more clear:

1. To get the total number of reports I have received in so far (Column B), I can do a COUNTA(range). Now, I need to know of that COUNTA(range), how many were assigned to Analyst A, assigned to Analyst B, etc (Column A).
2. Along those same lines, once I get the answer to number 1, I need to know how many of those that we have received in assigned to Analyst A, how many has Analyst A input into the data base (column C).

Thanks
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-09-03 11:14, mmlugar wrote:
Here’s the situation:
I have three columns on the spreadsheet.
Column 1 = This has the initials of the analyst who is assigned responsibility for overseeing a report. (i.e. JJC is responsible for Report GK, JLL is responsible for Report LLR, etc)
Column 2 = The Intern (not the analyst) inserts his/her initials here as soon as the report enters our office. Otherwise, this cell(s) is blank.
Column 3 = The Analyst inserts his/her initials here as soon as he/she enters the report in the database. Otherwise, this cell(s) is blank.

Here’s what I need:

How many reports have entered our office by analyst (i.e. how many reports assigned to JJC have entered the office?).

How many reports assigned to said analyst have been entered into the database?

Eventually I will need these broken down into %, but that should be easy.

This is definitely a counting function issue, but I haven’t been able to get DCOUNTA or COUNTIF to do anything. Your help is much appreciated!

Why don't you simply state what the ranges of interest are -- are these secret?
This message was edited by Aladin Akyurek on 2002-09-03 11:53
 

maxflia10

Well-known Member
Joined
May 20, 2002
Messages
890

ADVERTISEMENT

On 2002-09-03 11:32, mmlugar wrote:
Not quite what I am looking for. Let me be more clear:

1. To get the total number of reports I have received in so far (Column B), I can do a COUNTA(range). Now, I need to know of that COUNTA(range), how many were assigned to Analyst A, assigned to Analyst B, etc (Column A).
2. Along those same lines, once I get the answer to number 1, I need to know how many of those that we have received in assigned to Analyst A, how many has Analyst A input into the data base (column C).

Thanks

To get a count of how many reports to what Analyst

=countif(range,"analyst a")

then use sumproduct for the other example
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-09-03 11:32, mmlugar wrote:
Not quite what I am looking for. Let me be more clear:

1. To get the total number of reports I have received in so far (Column B), I can do a COUNTA(range). Now, I need to know of that COUNTA(range), how many were assigned to Analyst A, assigned to Analyst B, etc (Column A).
2. Along those same lines, once I get the answer to number 1, I need to know how many of those that we have received in assigned to Analyst A, how many has Analyst A input into the data base (column C).

Thanks

Let A2:A20 house the report assignments, B2:B20 the assignments which made it to the office, and C2:C20 the assignments which have been entered into DB.

Make a unique list of analysts in E from E2 on.

In F2 enter & copy down:

=SUMPRODUCT(($A$2:$A$20=E2)*(LEN($B$2:$B$20)>0))

will give you the count of reports by an analyst which made it to the office.

In G2 enter & copy down:

=SUMPRODUCT(($A$2:$A$20=E2)*($C$2:$C$20=E2))

will give you the count of reports that is assigned to an analyst & entered into DB by the analyst.
 

Forum statistics

Threads
1,144,220
Messages
5,723,085
Members
422,477
Latest member
pete101

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?

Disable AdBlock

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
Top