Help! I need a formula!

kopernikuz

New Member
Joined
Mar 21, 2002
Messages
7
Here's the situation, for example sake sheet one has the following data columns:

Name Account Status
Joe 1234 A
Joe 7896 A
Joe 5784 D
Joe 1432 P
Joe 3285 A
Mary 9999 A
Mary 4444 A
Mary 1964 D

On sheet 2, I need to list each "Name" and count how many "A" (active status code) appear for each name and then how many "D or P" (inactive status codes) appear for each name.

For the above example:

Name Active Inactive
Joe 3 2
Mary 2 1

Can anyone advise me on this? Thanks!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Use a PivotTable. The Status field can be grouped into 'Active' and 'Inactive' categories. The results look something like this...

Count of Name Status
Name Active Inactive
Joe 3 2
Mary 2 1

Note: You'll have to view these results in edit mode to see proper spacing.
This message was edited by Mark W. on 2002-03-22 09:17
 
Upvote 0
With Name in E2
=SUMPRODUCT(($A$2:$A$9=E2)*($C$2:$C$9="A"))
 
Upvote 0
I've never used a pivot table before... trying it now, and I can kind of see how it would help, but am having trouble figuring out how to define the columns and counts
 
Upvote 0
Wait, think I got it to work... however, in using Pivot tables... I want to add one more field. How do I do it?

I want to calculate the difference between active and inactive, to get a net and include it in the table. What steps do I take to add this column as well

Name Active Inactive Var
Joe 3 2 1
Mary 2 1 1

I got the name and status columns working... but how do I add this last one?
 
Upvote 0
On 2002-03-22 09:28, kopernikuz wrote:
I've never used a pivot table before... trying it now, and I can kind of see how it would help, but am having trouble figuring out how to define the columns and counts

Hi kopernikuz,

Don't give up! Pivot Tables are one of the best features of Excel, and it would be well worth your time and effort to learn how to use them.

http://www.cpearson.com/excel/pivots.htm
is a great resource to get you started.

Also, I think the MrExcel site proper has some nice tips for the advanced features of PivotTables.

Good luck,
Jay
 
Upvote 0
On 2002-03-22 09:35, kopernikuz wrote:
Wait, think I got it to work... however, in using Pivot tables... I want to add one more field. How do I do it?

I want to calculate the difference between active and inactive, to get a net and include it in the table. What steps do I take to add this column as well

Name Active Inactive Var
Joe 3 2 1
Mary 2 1 1

I got the name and status columns working... but how do I add this last one?

I think I can get you fairly close...

1. Add another "Count of Name" to the PivotTable's DATA area.
2. While still in the Wizard double-click this new DATA field button, press the [Options>>] button, choose Show data as "Difference From" with a Base field of "Status2" (your grouped status code) and a Base item of "Inactive".
3. This will add 2 new columns to your PivotTable. Hide the 2nd column.
4. Change the formatting of the cell containing "Active" in the 3rd column to ;;;"Var"
5. Change the formatting of the cell contain "Count of Name2" to ;;;

Note: You'll need to Enable Selection (see the PivotTable | Select menu on the PivotTable toolbar) to preserve this formatting after Refresh.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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
Back
Top