M
Marcelo
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?
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?
26)-SUM(E$25:E25)