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?

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?

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?

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?