SOLVED

pivot table grouping

%3CLINGO-SUB%20id%3D%22lingo-sub-1321506%22%20slang%3D%22en-US%22%3Epivot%20table%20grouping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1321506%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3BI%20want%20to%20count%20how%20many%20rows%20in%20my%20book%20that%20have%20values%20between%20the%20below%20values.%20How%20can%20I%20do%20this%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22values.PNG%22%20style%3D%22width%3A%20134px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F185377iA6D85D2266F84AC1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22values.PNG%22%20alt%3D%22values.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1321506%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1321690%22%20slang%3D%22en-US%22%3ERe%3A%20pivot%20table%20grouping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1321690%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F629236%22%20target%3D%22_blank%22%3E%40Michuk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.google.com%2Fsearch%3Fnewwindow%3D1%26amp%3Bsxsrf%3DALeKk01mNQ--bHtJCpKh1iB-xQ-8UkJPkg%253A1587393216535%26amp%3Bei%3DwLKdXqWmIJG4kwXH7q_gCw%26amp%3Bq%3Dgrouping%2Bnumbers%2Bin%2Ba%2Bpivot%2Btable%26amp%3Boq%3DGrouping%2Bnumbers%2Bin%2Ba%2Bpivot%2Bta%26amp%3Bgs_lcp%3DCgZwc3ktYWIQAxgAMgYIABAWEB4yBggAEBYQHjIGCAAQFhAeMgYIABAWEB4yBggAEBYQHjIGCAAQFhAeOgQIABBHOgQIIRAVOgYIABAIEB46BggjECcQEzoFCAAQywE6CAghEBYQHRAeUOqHBVjDswVg_sYFaABwBXgBgAHUAYgBvBCSAQYwLjE0LjGYAQCgAQGqAQdnd3Mtd2l6%26amp%3Bsclient%3Dpsy-ab%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EGrouping%20numbers%20in%20a%20pivot%20table.%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1324347%22%20slang%3D%22en-US%22%3ERe%3A%20pivot%20table%20grouping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1324347%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20managed%20to%20get%20the%20below%20result%2C%20but%20some%20values%20are%20in%20duplicate%20rows%2C%20for%20example%2C%20the%20value%20of%2031%20is%20in%20row%202%20and%203.%20How%20do%20I%20have%20unique%20values%20on%20each%20row%20as%20my%20above%20image%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Michuk_0-1587466110372.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F185575i98968BD175C5BC62%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Michuk_0-1587466110372.png%22%20alt%3D%22Michuk_0-1587466110372.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1324403%22%20slang%3D%22en-US%22%3ERe%3A%20pivot%20table%20grouping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1324403%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F629236%22%20target%3D%22_blank%22%3E%40Michuk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20concerns%20only%20the%20labels.%20You%20can%20check%20your%20source%20data.%20If%20it%20contains%20218%20values%2Frows%20then%20the%20result%20is%20valid.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMike%20Girvin%20talked%20about%20this%20bug%20in%20a%20video%20a%20long%20time%20ago.%20Maybe%20you%20can%20find%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1325156%22%20slang%3D%22en-US%22%3ERe%3A%20pivot%20table%20grouping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1325156%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20overwritten%20the%20text%20in%20the%20groups%20and%20the%20values%20are%20correct.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1850229%22%20slang%3D%22en-US%22%3ERe%3A%20pivot%20table%20grouping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1850229%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%26nbsp%3B%3CSPAN%3EI%20am%20looking%20to%20format%20Pivot%20Table%20grouping.%26nbsp%3B%20%26nbsp%3BSales%20grouping%20is%20in%20USD.%26nbsp%3B%20However%20when%20trying%20to%20use%20accounting%20format%20(or%20a%20currency)%26nbsp%3B%20it%20does%20not%20show%20up.%26nbsp%3B%20Any%20way%20around%20this%20please%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1851221%22%20slang%3D%22en-US%22%3ERe%3A%20pivot%20table%20grouping%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1851221%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F629236%22%20target%3D%22_blank%22%3E%40Michuk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20labels%20are%20text.%20You%20can't%20use%20a%20number%20format%20on%20text.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

 I want to count how many rows in my book that have values between the below values. How can I do this? 

 

values.PNG

6 Replies

@Detlef Lewin 

 

I managed to get the below result, but some values are in duplicate rows, for example, the value of 31 is in row 2 and 3. How do I have unique values on each row as my above image?

 

Thanks

 

Michuk_0-1587466110372.png

 

Best Response confirmed by Michuk (New Contributor)
Solution

@Michuk 

That concerns only the labels. You can check your source data. If it contains 218 values/rows then the result is valid.

 

Mike Girvin talked about this bug in a video a long time ago. Maybe you can find it.

 

@Detlef Lewin 

 

I have overwritten the text in the groups and the values are correct.

 

Many thanks 

 

@Detlef Lewin  I am looking to format Pivot Table grouping.   Sales grouping is in USD.  However when trying to use accounting format (or a currency)  it does not show up.  Any way around this please?

@Michuk 

The labels are text. You can't use a number format on text.