Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Slicing Data (Alternative to SUMIFS)
I’m having some trouble with Excel. In essence, I’m trying to perform
SUMIFS without using the formula (due to backwards incompatibility with 2003). Below I describe the problem and the things I’ve attempted so far to no avail. I have a table with data as such: Table1 Color Size Shape Quantity Blue Large Square 1 Blue Large Circle 2 Blue Small Square 3 Blue Small Circle 4 Red Large Square 5 Red Large Circle 6 Red Small Square 7 Red Small Circle 8 And I want to set up a table like such (with 20 rows): Table2 Color Size Shape Sum of Quantity Red Small 15 Where the user could input values (assumed to be valid or blank) for none, one, two or three of the characteristics. Excel 2007 solves this really easily by using SUMIFS and passing a star ‘*’ instead of blanks in the list of parameters (interpreted to mean “all”). My problem, ladies and gentlemen, is that the end user is still in Excel 2003. I have tried the following: Sumproduct Used the SumProduct formula =SUMPRODUCT(Sum_range*Criteria1[*Criteria2][*Criteria3]...) where criteria# is a conditional like so: (A2:A9=”Blue”) or (A2:A9=F2) This works except ALL three of the parameters must have inputs or it returns a total of 0 because it tries to match the empty string (“”) in the data source (and none of the records in Table1 are empty strings). Gap: How to setup the criteria statement to interpret the blank field as ‘all entries’. Pivot Table Created a pivot table showing the data and then used the GetPivotData formula =GETPIVOTDATA(data_field,pivot_table,field1,item1, field2,item2,...) This works except ALL three of the parameters must have inputs or it returns a #REF error. Gap: How to tell GetPivotData to choose all in one category (analogous to the ‘*’ in the SUMIFS) Conceivably I could set up a pivot table for every row (20 of them) on the Table2 (above) and the 32 copies of Table2 that I’ll need to create (640 pivot tables…). However, that would be incredibly cumbersome from a creation, usability and maintenance perspective. Custom formula In the past I dealt with incompatible formulas by writing a custom formula in VBA. This was for IFERROR which is a much simpler algorithm. I have been unable to find code emulating SUMIFS on the web. Ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Slicing Data (Alternative to SUMIFS)
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Slicing Data (Alternative to SUMIFS)
Hi,
Assume that the data is in range b14:E22 (including the header row). Copy b14:E14 to B25:E25. Type Red in B26 and Small in C26. Now in E26, just use =DSUM(B14:E22,E25,B25:D26)-SUM(E$25:E25) You can now copy this formula down. Hope this helps -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Marcelo" wrote in message ... I’m having some trouble with Excel. In essence, I’m trying to perform SUMIFS without using the formula (due to backwards incompatibility with 2003). Below I describe the problem and the things I’ve attempted so far to no avail. I have a table with data as such: Table1 Color Size Shape Quantity Blue Large Square 1 Blue Large Circle 2 Blue Small Square 3 Blue Small Circle 4 Red Large Square 5 Red Large Circle 6 Red Small Square 7 Red Small Circle 8 And I want to set up a table like such (with 20 rows): Table2 Color Size Shape Sum of Quantity Red Small 15 Where the user could input values (assumed to be valid or blank) for none, one, two or three of the characteristics. Excel 2007 solves this really easily by using SUMIFS and passing a star ‘*’ instead of blanks in the list of parameters (interpreted to mean “all”). My problem, ladies and gentlemen, is that the end user is still in Excel 2003. I have tried the following: Sumproduct Used the SumProduct formula =SUMPRODUCT(Sum_range*Criteria1[*Criteria2][*Criteria3]...) where criteria# is a conditional like so: (A2:A9=”Blue”) or (A2:A9=F2) This works except ALL three of the parameters must have inputs or it returns a total of 0 because it tries to match the empty string (“”) in the data source (and none of the records in Table1 are empty strings). Gap: How to setup the criteria statement to interpret the blank field as ‘all entries’. Pivot Table Created a pivot table showing the data and then used the GetPivotData formula =GETPIVOTDATA(data_field,pivot_table,field1,item1, field2,item2,...) This works except ALL three of the parameters must have inputs or it returns a #REF error. Gap: How to tell GetPivotData to choose all in one category (analogous to the ‘*’ in the SUMIFS) Conceivably I could set up a pivot table for every row (20 of them) on the Table2 (above) and the 32 copies of Table2 that I’ll need to create (640 pivot tables…). However, that would be incredibly cumbersome from a creation, usability and maintenance perspective. Custom formula In the past I dealt with incompatible formulas by writing a custom formula in VBA. This was for IFERROR which is a much simpler algorithm. I have been unable to find code emulating SUMIFS on the web. Ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumifs formula compairing data on different lines | Excel Discussion (Misc queries) | |||
SUMIFs for particular cells in blocks of data | Excel Worksheet Functions | |||
I need alternative formula for SUMIFS in Excel 2003 | Excel Worksheet Functions | |||
Slicing Pie Charts | Excel Discussion (Misc queries) | |||
Alternative to using IF function to extract data | Excel Discussion (Misc queries) |