I have a worksheet in a workbook where I need to get a sum from a range of
cells from another worksheet in the same workbook. However, I only want to
sum the values in that range if two different sets of criteria are met on
each row. Example: Worksheet1 cell B5 needs the formula. I want it to sum
all values from worksheet2 column D, only if the cells in column B and column
C on any given row (on worksheet2) match the values in worksheet1 cells A1
and B3 respectively. So for cell D3 to be counted in the sum B3(worksheet2)
must = A1(worksheet1) AND C3(worksheet2) must = B3(worksheet1). As far as I
can tell the SumIf function will only allow you to use one criteria, is there
a way around this?

=sumproduct(--(yourfirstrange=variable)*(yoursecondrange=2ndvari able),(range))

range = the area to sum

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"JudyT" escreveu:

I have a worksheet in a workbook where I need to get a sum from a range of
cells from another worksheet in the same workbook. However, I only want to
sum the values in that range if two different sets of criteria are met on
each row. Example: Worksheet1 cell B5 needs the formula. I want it to sum
all values from worksheet2 column D, only if the cells in column B and column
C on any given row (on worksheet2) match the values in worksheet1 cells A1
and B3 respectively. So for cell D3 to be counted in the sum B3(worksheet2)
must = A1(worksheet1) AND C3(worksheet2) must = B3(worksheet1). As far as I
can tell the SumIf function will only allow you to use one criteria, is there
a way around this?

I guess this information should have been included, but the criteria that
need to be matched are text fields. When I use the formula below I receive a
#NUM! error.

"Marcelo" wrote:


=sumproduct(--(yourfirstrange=variable)*(yoursecondrange=2ndvari able),(range))

range = the area to sum

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"JudyT" escreveu:

I have a worksheet in a workbook where I need to get a sum from a range of
cells from another worksheet in the same workbook. However, I only want to
sum the values in that range if two different sets of criteria are met on
each row. Example: Worksheet1 cell B5 needs the formula. I want it to sum
all values from worksheet2 column D, only if the cells in column B and column
C on any given row (on worksheet2) match the values in worksheet1 cells A1
and B3 respectively. So for cell D3 to be counted in the sum B3(worksheet2)
must = A1(worksheet1) AND C3(worksheet2) must = B3(worksheet1). As far as I
can tell the SumIf function will only allow you to use one criteria, is there
a way around this?

could you please send your formula?
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"JudyT" escreveu:

I guess this information should have been included, but the criteria that
need to be matched are text fields. When I use the formula below I receive a
#NUM! error.

"Marcelo" wrote:


=sumproduct(--(yourfirstrange=variable)*(yoursecondrange=2ndvari able),(range))

range = the area to sum

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"JudyT" escreveu:

I have a worksheet in a workbook where I need to get a sum from a range of
cells from another worksheet in the same workbook. However, I only want to
sum the values in that range if two different sets of criteria are met on
each row. Example: Worksheet1 cell B5 needs the formula. I want it to sum
all values from worksheet2 column D, only if the cells in column B and column
C on any given row (on worksheet2) match the values in worksheet1 cells A1
and B3 respectively. So for cell D3 to be counted in the sum B3(worksheet2)
must = A1(worksheet1) AND C3(worksheet2) must = B3(worksheet1). As far as I
can tell the SumIf function will only allow you to use one criteria, is there
a way around this?

I have been through mutiple variations, this is where I am now:

=SUMPRODUCT(--(RDS_DATA!$K:$K=B$4&B$5&$A127)*(RDS_DATA!$J:$J)+SU MPRODUCT(--(RDS_DATA!$C:$C=RIGHT(A4,5))*(RDS_DATA!$J:$J)))

"Marcelo" wrote:

could you please send your formula?
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"JudyT" escreveu:

I guess this information should have been included, but the criteria that
need to be matched are text fields. When I use the formula below I receive a
#NUM! error.

"Marcelo" wrote:


=sumproduct(--(yourfirstrange=variable)*(yoursecondrange=2ndvari able),(range))

range = the area to sum

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"JudyT" escreveu:

I have a worksheet in a workbook where I need to get a sum from a range of
cells from another worksheet in the same workbook. However, I only want to
sum the values in that range if two different sets of criteria are met on
each row. Example: Worksheet1 cell B5 needs the formula. I want it to sum
all values from worksheet2 column D, only if the cells in column B and column
C on any given row (on worksheet2) match the values in worksheet1 cells A1
and B3 respectively. So for cell D3 to be counted in the sum B3(worksheet2)
must = A1(worksheet1) AND C3(worksheet2) must = B3(worksheet1). As far as I
can tell the SumIf function will only allow you to use one criteria, is there
a way around this?

sumproduct does not work with the full column range A:A

use a2:A5000 for the first b2:b5000 etc, for eg

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"JudyT" escreveu:

I have been through mutiple variations, this is where I am now:

=SUMPRODUCT(--(RDS_DATA!$K:$K=B$4&B$5&$A127)*(RDS_DATA!$J:$J)+SU MPRODUCT(--(RDS_DATA!$C:$C=RIGHT(A4,5))*(RDS_DATA!$J:$J)))

"Marcelo" wrote:

could you please send your formula?
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"JudyT" escreveu:

I guess this information should have been included, but the criteria that
need to be matched are text fields. When I use the formula below I receive a
#NUM! error.

"Marcelo" wrote:


=sumproduct(--(yourfirstrange=variable)*(yoursecondrange=2ndvari able),(range))

range = the area to sum

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"JudyT" escreveu:

I have a worksheet in a workbook where I need to get a sum from a range of
cells from another worksheet in the same workbook. However, I only want to
sum the values in that range if two different sets of criteria are met on
each row. Example: Worksheet1 cell B5 needs the formula. I want it to sum
all values from worksheet2 column D, only if the cells in column B and column
C on any given row (on worksheet2) match the values in worksheet1 cells A1
and B3 respectively. So for cell D3 to be counted in the sum B3(worksheet2)
must = A1(worksheet1) AND C3(worksheet2) must = B3(worksheet1). As far as I
can tell the SumIf function will only allow you to use one criteria, is there
a way around this?