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?

try
=sumproduct((a2:a22="blue")*(b2:b22="small")*d2:d2 2)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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?

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,B2526)-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?