Complex SUMIF statement
 

all,
I am trying to use a SUMIF statement to pull a column of data if it
meets 2 criteria.
sheet 1 and sheet 2, sheet 1 has the data and a criteria, sheet 2 has a
criteria and the formula.

I am trying to see if a row in sheet 1 matches a criteria like "US" or
"BR" then I am trying to take that same row in sheet 1, if another
column in the row sheet 1 meets another criteria of sheet 2, take the
data from a third column in sheet 1 and input it into the sheet 2.

I am using the following formula and its not working.
=SUM(IF(('Sheet 1'!$S$2:$S$78<"BR")*('Sheet 1'!$X$2:$X$78='Sheet
2'!$B5),'Sheet 1'!M$2:M$78))

If I can get this to work for Sheet 1 then I need to do this for
another 5 sheets...
is there a limit to the character length of a formula?

Complex SUMIF statement
 

Sumif really does not do well with multiple cirteria. You shoul look at using
sumproduct... Take a look at this site for more info...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

To answer your question though you are no where near the limit of length for
a formula.
--
HTH...

Jim Thomlinson


" wrote:

all,
I am trying to use a SUMIF statement to pull a column of data if it
meets 2 criteria.
sheet 1 and sheet 2, sheet 1 has the data and a criteria, sheet 2 has a
criteria and the formula.

I am trying to see if a row in sheet 1 matches a criteria like "US" or
"BR" then I am trying to take that same row in sheet 1, if another
column in the row sheet 1 meets another criteria of sheet 2, take the
data from a third column in sheet 1 and input it into the sheet 2.

I am using the following formula and its not working.
=SUM(IF(('Sheet 1'!$S$2:$S$78<"BR")*('Sheet 1'!$X$2:$X$78='Sheet
2'!$B5),'Sheet 1'!M$2:M$78))

If I can get this to work for Sheet 1 then I need to do this for
another 5 sheets...
is there a limit to the character length of a formula?