A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Counting from multiple fields



 
 
Thread Tools Display Modes
  #1  
Old October 5th, 2009, 07:31 PM posted to microsoft.public.excel.misc
toby131
external usenet poster
 
Posts: 15
Default Counting from multiple fields

Each record I have has 7 different serial number fields. Some records just
have 1 serial number, while others might have multiple. My fields are named
SN1, SN2, SN3, etc, but regardless of which field the # is in, it is still
essentially the same. I am making a pivot table where I want to count how
many total serial numbers fall into each category of record type. The only
things I have tried so far give me the format shown below, but I need a
single count instead of seperate counts. Is this possible?

Category Data Total
Category A Count of SN1 3
Count of SN2 8
Count of SN3 2
Count of SN4 5
Count of SN5 6
Count of SN6 2
Count of SN7 17
Category B Count of SN1 6
Count of SN2 9
Count of SN3 5
Count of SN4 3
Count of SN5 9
Count of SN6 16
Count of SN7 5
Category C Count of SN1 9
Count of SN2 14
Count of SN3 7
Count of SN4 3
Count of SN5 13
Count of SN6 6
Count of SN7 3


  #2  
Old October 6th, 2009, 01:13 AM posted to microsoft.public.excel.misc
Herbert Seidenberg
external usenet poster
 
Posts: 1,113
Default Counting from multiple fields

Excel 2007 PivotTable
Three count modes.
http://www.mediafire.com/file/4zzwdzzqmzt/10_05_09.xlsx
  #3  
Old October 6th, 2009, 07:15 AM posted to microsoft.public.excel.misc
Hardeep kanwar[_2_]
external usenet poster
 
Posts: 14
Default Counting from multiple fields

Hey Nice Solution

I have never seen the total in Upper Side in Pivot Table. It always is in
the Last Cell of the Range

I try Lots but couldn't Make Pivot Table Like you.

Could you Pls Guide me step by Step

Thanks in Advance

Hardeep Kanwar



"Herbert Seidenberg" wrote:

Excel 2007 PivotTable
Three count modes.
http://www.mediafire.com/file/4zzwdzzqmzt/10_05_09.xlsx

  #4  
Old October 6th, 2009, 02:52 PM posted to microsoft.public.excel.misc
toby131
external usenet poster
 
Posts: 15
Default Counting from multiple fields

This is what I am looking for, but I don't understand exactly what you did.
I'm working with Excel 2003. How do I get that SN Count to have the total
across from the Cat and not just the list below it? Thanks!

"Herbert Seidenberg" wrote:

Excel 2007 PivotTable
Three count modes.
http://www.mediafire.com/file/4zzwdzzqmzt/10_05_09.xlsx

  #5  
Old October 6th, 2009, 05:25 PM posted to microsoft.public.excel.misc
Herbert Seidenberg
external usenet poster
 
Posts: 1,113
Default Counting from multiple fields

Excel 2007 PivotTable
The format of the PT is unique to 2007
See the tab "Help":
http://www.mediafire.com/file/mwoijg...10_05_09a.xlsx

  #6  
Old October 7th, 2009, 01:51 PM posted to microsoft.public.excel.misc
Hardeep kanwar[_2_]
external usenet poster
 
Posts: 14
Default Counting from multiple fields

Awesome Man,

I See many Post in which you Replies and the most common thing in your
Replies is "PIVOT TABLE"

You are Gr8 in PIVOT TABLE.

If you Don't Mind Could you Pls Provide me the Example of Pivot Table.

Because the Way you Explain in your Example that is very Simple and Clear.

Thanks in Advance

Hardeep kanwar

"Herbert Seidenberg" wrote:

Excel 2007 PivotTable
The format of the PT is unique to 2007
See the tab "Help":
http://www.mediafire.com/file/mwoijg...10_05_09a.xlsx


  #7  
Old October 8th, 2009, 06:37 AM posted to microsoft.public.excel.misc
Hardeep kanwar[_2_]
external usenet poster
 
Posts: 14
Default Counting from multiple fields

Hi!

I try Lots but Could not Find what you have done.

When i try to make Pivot table, In my Pivot Table the Field Shown as
"Row",Column",Value".

But in your Pivot Table it show

"Cat","SNs","Value"

I try Lots n lots, I make me Mad






"Herbert Seidenberg" wrote:

Excel 2007 PivotTable
The format of the PT is unique to 2007
See the tab "Help":
http://www.mediafire.com/file/mwoijg...10_05_09a.xlsx


  #8  
Old October 8th, 2009, 03:56 PM posted to microsoft.public.excel.misc
Herbert Seidenberg
external usenet poster
 
Posts: 1,113
Default Counting from multiple fields

Excel 2007
The default PT field names (Row,Column,Value)
can be renamed to anything else (MyCategory, Serial#, ...)
but don't use the source names (Sn1, Sn2, ...)
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:15 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.