Help! I need a formula!

[kopernikuz]

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!

[Mark W.]

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 ]

[kopernikuz]

Any advice for someone not familiar with pivot tables? Sorry.

[Dave Patton]

With Name in E2
=SUMPRODUCT(($A$2:$A$9=E2)*($C$2:$C$9="A"))

[Mark W.]

On 2002-03-22 09:12, kopernikuz wrote:
Any advice for someone not familiar with pivot tables? Sorry.

See my edited response above.

[kopernikuz]

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

[kopernikuz]

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?

[Jay Petrulis]

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

[Mark W.]

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.