SUMIF/SUMPRODUCT Criteria are Variable Sized
 

Hello all,

Currently I am attempting to pull a number from another tab in my workbook
that is a compilation of sub-grouped items. The coding that I was attempting
was:

=SUMIF(Smithfield!A:A,Combined!A14:A23,Smithfield! I:I)

Which will not work due to the multiple criteria. I have also attempted a
SUMPRODUCT code, but because the two arrays are not the same size, a #VALUE
or #NUM value is returned. Let's example here (n is a place holder for any
number):

Smithfield tab:
Col A Col I
5 0
1 20
2 15
2 20
12 0
12 17
3 36
16 52
n etc...

Combined tab:
Col A
12
5
3
7
n

In short, one tab holds all data with multiple groups, another combines like
groups together for a single number. The data on the Combined tab and on the
Smithfield tab can be in any order. As the two arrays A:A and A14:A23 are
not the same size, neither the SUMPRODUCT nor the SUMIF will allow it. I am
looking to pull ONE number as a summation of a group listing (as it is not
the entire list, but a section of it), so if there are 65 groups and I want
to see groups that are listed in the section described (which does not have
to be in a specific order--could be 17,2,5,3,4,22,60,33,etc. in the group I
want to look at), I want to pull one number for the sum of those specified
groups.

Any ideas on how to accomplish this feat?

SUMIF/SUMPRODUCT Criteria are Variable Sized
 

Try this:

=SUMPRODUCT(--(ISNUMBER(MATCH(Smithfield!A1:A100,Combined!A14:A2 3,0))),Smithfield!I1:I100)

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007.

--
Biff
Microsoft Excel MVP


"Thomas [PBD]" wrote in message
...
Hello all,

Currently I am attempting to pull a number from another tab in my workbook
that is a compilation of sub-grouped items. The coding that I was
attempting
was:

=SUMIF(Smithfield!A:A,Combined!A14:A23,Smithfield! I:I)

Which will not work due to the multiple criteria. I have also attempted a
SUMPRODUCT code, but because the two arrays are not the same size, a
#VALUE
or #NUM value is returned. Let's example here (n is a place holder for
any
number):

Smithfield tab:
Col A Col I
5 0
1 20
2 15
2 20
12 0
12 17
3 36
16 52
n etc...

Combined tab:
Col A
12
5
3
7
n

In short, one tab holds all data with multiple groups, another combines
like
groups together for a single number. The data on the Combined tab and on
the
Smithfield tab can be in any order. As the two arrays A:A and A14:A23 are
not the same size, neither the SUMPRODUCT nor the SUMIF will allow it. I
am
looking to pull ONE number as a summation of a group listing (as it is not
the entire list, but a section of it), so if there are 65 groups and I
want
to see groups that are listed in the section described (which does not
have
to be in a specific order--could be 17,2,5,3,4,22,60,33,etc. in the group
I
want to look at), I want to pull one number for the sum of those specified
groups.

Any ideas on how to accomplish this feat?

SUMIF/SUMPRODUCT Criteria are Variable Sized
 

Works great. MATCH(), haven't seen it before. Will definitely be using that
more often.

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(ISNUMBER(MATCH(Smithfield!A1:A100,Combined!A14:A2 3,0))),Smithfield!I1:I100)

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007.

--
Biff
Microsoft Excel MVP


"Thomas [PBD]" wrote in message
...
Hello all,

Currently I am attempting to pull a number from another tab in my workbook
that is a compilation of sub-grouped items. The coding that I was
attempting
was:

=SUMIF(Smithfield!A:A,Combined!A14:A23,Smithfield! I:I)

Which will not work due to the multiple criteria. I have also attempted a
SUMPRODUCT code, but because the two arrays are not the same size, a
#VALUE
or #NUM value is returned. Let's example here (n is a place holder for
any
number):

Smithfield tab:
Col A Col I
5 0
1 20
2 15
2 20
12 0
12 17
3 36
16 52
n etc...

Combined tab:
Col A
12
5
3
7
n

In short, one tab holds all data with multiple groups, another combines
like
groups together for a single number. The data on the Combined tab and on
the
Smithfield tab can be in any order. As the two arrays A:A and A14:A23 are
not the same size, neither the SUMPRODUCT nor the SUMIF will allow it. I
am
looking to pull ONE number as a summation of a group listing (as it is not
the entire list, but a section of it), so if there are 65 groups and I
want
to see groups that are listed in the section described (which does not
have
to be in a specific order--could be 17,2,5,3,4,22,60,33,etc. in the group
I
want to look at), I want to pull one number for the sum of those specified
groups.

Any ideas on how to accomplish this feat?

SUMIF/SUMPRODUCT Criteria are Variable Sized
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Thomas [PBD]" wrote in message
...
Works great. MATCH(), haven't seen it before. Will definitely be using
that
more often.

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(ISNUMBER(MATCH(Smithfield!A1:A100,Combined!A14:A2 3,0))),Smithfield!I1:I100)

Note that with SUMPRODUCT you *can't* use entire columns as range
references
unless you're using Excel 2007.

--
Biff
Microsoft Excel MVP


"Thomas [PBD]" wrote in message
...
Hello all,

Currently I am attempting to pull a number from another tab in my
workbook
that is a compilation of sub-grouped items. The coding that I was
attempting
was:

=SUMIF(Smithfield!A:A,Combined!A14:A23,Smithfield! I:I)

Which will not work due to the multiple criteria. I have also
attempted a
SUMPRODUCT code, but because the two arrays are not the same size, a
#VALUE
or #NUM value is returned. Let's example here (n is a place holder for
any
number):

Smithfield tab:
Col A Col I
5 0
1 20
2 15
2 20
12 0
12 17
3 36
16 52
n etc...

Combined tab:
Col A
12
5
3
7
n

In short, one tab holds all data with multiple groups, another combines
like
groups together for a single number. The data on the Combined tab and
on
the
Smithfield tab can be in any order. As the two arrays A:A and A14:A23
are
not the same size, neither the SUMPRODUCT nor the SUMIF will allow it.
I
am
looking to pull ONE number as a summation of a group listing (as it is
not
the entire list, but a section of it), so if there are 65 groups and I
want
to see groups that are listed in the section described (which does not
have
to be in a specific order--could be 17,2,5,3,4,22,60,33,etc. in the
group
I
want to look at), I want to pull one number for the sum of those
specified
groups.

Any ideas on how to accomplish this feat?