Hi all,
Is there a way to get a pivot table to show me records that meet ANY not ALL of my filters?
My problem:
I have built a summary table in Excel which looks at a data-set and performs various counts and sums against the data-set to give me a view by year, month and selected customer for a set of various metrics (number of calls, emails, support requests etc per month).
I can get a single number for my criteria using countifs and sumifs (with nested ifs and what not), but what I cannot do is get a pivot table or otherwise to return me with the same records that I can count or sum. The data set I am using is not as clean as is desirable so a single row is assessed on a few criteria, but it's not as simple as saying:
"Give me the total support time spent and the number of support tickets for ABC Electronics where the month is January and the year is 2011."
In a pivot I could do that because I am asking for ALL my criteria to be matched, but I have this kind of query where I am asking for ANY of my criteria to be matched:
SUM (
SUMIFS ( MyColumnToSum1, CustomerColumn, "ABC Electronics", SupportColumn, "Support Ticket", YearColumn, "2011", MonthColumn1, "Janaury", SupportTypeColumn, "<>Defects" ) ,
SUMIFS ( MyColumnToSum2, CustomerColumn, "ABC Electronics", SupportColumn, "Support Ticket", YearColumn, "2011", MonthColumn2, "Janaury", SupportTypeColumn, "<>Defects" ) ,
SUMIFS ( MyColumnToSum3, CustomerColumn, "ABC Electronics", SupportColumn, "Support Ticket", YearColumn, "2011", MonthColumn3, "Janaury", SupportTypeColumn, "<>Defects" )
)
I am summing up the total hours spent per customer, per month, per year across three separate fields in a single row - MonthColumn1, MonthColumn2 and MonthColumn3, the date values correspond to time spent for that month.
What I've been trying to do is turn that single-number-query into a list of rows that are retrieved so I can see what the Support Tickets were, but I can't work out how to make a pivot table use an OR statement rather than an AND statement in the selection criteria.
Any pointers?
Cheers,
dickohead
Is there a way to get a pivot table to show me records that meet ANY not ALL of my filters?
My problem:
I have built a summary table in Excel which looks at a data-set and performs various counts and sums against the data-set to give me a view by year, month and selected customer for a set of various metrics (number of calls, emails, support requests etc per month).
I can get a single number for my criteria using countifs and sumifs (with nested ifs and what not), but what I cannot do is get a pivot table or otherwise to return me with the same records that I can count or sum. The data set I am using is not as clean as is desirable so a single row is assessed on a few criteria, but it's not as simple as saying:
"Give me the total support time spent and the number of support tickets for ABC Electronics where the month is January and the year is 2011."
In a pivot I could do that because I am asking for ALL my criteria to be matched, but I have this kind of query where I am asking for ANY of my criteria to be matched:
SUM (
SUMIFS ( MyColumnToSum1, CustomerColumn, "ABC Electronics", SupportColumn, "Support Ticket", YearColumn, "2011", MonthColumn1, "Janaury", SupportTypeColumn, "<>Defects" ) ,
SUMIFS ( MyColumnToSum2, CustomerColumn, "ABC Electronics", SupportColumn, "Support Ticket", YearColumn, "2011", MonthColumn2, "Janaury", SupportTypeColumn, "<>Defects" ) ,
SUMIFS ( MyColumnToSum3, CustomerColumn, "ABC Electronics", SupportColumn, "Support Ticket", YearColumn, "2011", MonthColumn3, "Janaury", SupportTypeColumn, "<>Defects" )
)
I am summing up the total hours spent per customer, per month, per year across three separate fields in a single row - MonthColumn1, MonthColumn2 and MonthColumn3, the date values correspond to time spent for that month.
What I've been trying to do is turn that single-number-query into a list of rows that are retrieved so I can see what the Support Tickets were, but I can't work out how to make a pivot table use an OR statement rather than an AND statement in the selection criteria.
Any pointers?
Cheers,
dickohead
