• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to get count for different criteria's (including overall)

P

Pavan_mtm

New Member
Hello All,

Could anyone help me in getting the count of the line items with multiple criteria's that includes overall for different columns. I have attached the data and the output format i need.

Thank you.
 

Attachments

  • Book1.xlsx
    359 KB · Views: 8
I do not see any cell labeled "overall combination", can you be more precise?
 
Hi Luke,

There is no separate column for Overall, it is a combination of all. If you look at the output sheet, it has all the combinations and that is how i need the data.

attached is the 1 combination.

Similarly we have multiple combinations in the output sheet.
 

Attachments

  • Book1.txt
    65 bytes · Views: 1
Last edited:
Which item controls the combinations, just the tier? If we did a true combination of all those fields, it would be (just for Feb 2017)
85 * 6 * 3 * 6 * 17 = 156,060 combinations to display.

I think it would be more efficient to leave as is. If you're wanting the information in a chart or something, you can use the GETPIVOTDATA function, and if it errors out, you know the answer is 0.
 
The total combinations are 1710 as per the reference column in the output sheet.

i just need the count as per the aging for the 1710 combinations.

Regards,
Pavan.
 
Which fields are you varying then to get the 1710 combinations? If you know what they are, in the PivotTable, you can right-click, Field Options, and choose to Show Items with no Data. That should get you closer.
 
Hi Luke,
Could you please filter reference column that contains "overall" and check other columns for combinations.

Regards,
Pavan.
 
I'm sorry, Pavan, I don't know what you mean. I was first pointing out that if your truly built all the combinations with the sample data, you'd have over 100k records. You then said you only wanted 1710, but I don't know what the logic is to get that number (which columns you allow to vary to get to that). You can change the PT to show all possible items, if you need that.

Additionally, you've again referred to an "overall" column, but previously confirmed that such a column doesn't exist. :(
 
Hi Luke ,

I think what he means is that he wants the output as he has shown in the worksheet tab named Output.

In this , for each of the fields Region , Document , Team / Scope and Tier , he has added a category called Overall.

For example , if we consider the Region ABCD , the document 5010 , the Team / Scope ABScope , and the tiers TIER 1 and TIER 2 , he has added the following :

ABCD5010ABScopeOverall - sum of ABCD5010ABScopeTier 1 and ABCD5010ABScopeTier 2.

ABCD5010OverallOverall - sum of ABCD5010ABScopeTier 1 , ABCD5010ABScopeTier 2 , ABCD5010XYZScopeTier 1 and ABCD5010XYZScopeStrategic 2.

And similar overall categories have been added for the Region and Document fields.

Thus , your pivot table has only 84 references (row labels) , while with the addition of the overall category to each of the fields , he has a total of 1710 references ! The final one is the category OverallOverallOverallOverall !

Narayan
 
You must log in or register to reply here.
Back
Top