SumIf with multiple Criteria

[JudyT]

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?

 

[Marcelo]

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

range = the area to sum

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



"JudyT" escreveu:

 

[JudyT]

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]

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



"JudyT" escreveu:

 

[JudyT]

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)+SUMPRODUCT(--(RDS_DATA!$C:$C=RIGHT(A4,5))*(RDS_DATA!$J:$J)))

 

[Marcelo]

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:

 

[JudyT]

I tried:
=SUMPRODUCT(--(RDS_DATA!$K$2:$K$5000=B$4&B$5&$A127)*(RDS_DATA!$J$2:$J$5000)+SUMPRODUCT(--(RDS_DATA!$C$2:$C$5000=RIGHT($A$4,5))*(RDS_DATA!$J$2:$J$5000)))

This does solve the #NUM! error; however, it does not solve my original
dilemma of having to meet two criteria at the same time. With this formula
everything that meets the first criteria is added then everything that meets
the second criteria is added. I need something that only adds the fields
that meet both of my criteria. I went back to your previous post and tried
the following you suggested:

=SUMPRODUCT(--(RDS_DATA!$K$2:$K$5000=B$4&B$5&$A127)*(RDS_DATA!$C$2:$C$5000=RIGHT($A$4,5)),(RDS_DATA!$J$2:$J$5000))

This came back with 0 when it shouldn't have, so something must not be
working. I'm not sure exactly what this version is saying, so I don't have
any ideas on why it isn't working.