I have two sheets. The one called CorpClaims and one called Summary, the
latter is where I want the answer. On CorpClaims is a list of data with
various headings such as DateofLoss, Amount, DescriptionofLoss etc

On Summary is a small table that has Description of Loss down the left in
Column A, in row 2 is the applicable month (the input would be 01/08/2009 for
August 09 and formatted as Aug 09). Then the value part of the table would be
the sum of all amounts that fall under the Description of Loss, (which is
defined in column AJ) that fall in a certain month.

=SUMIFS(CorpClaims!L2:L20000,CorpClaims!F2:F20000, "="&AJ6,text(CorpClaims!I2:I20000,"mmyyyy"),"="&te xt(B2,"mmyyyy"))

The formula works well until you add the second criteria, then I get a
formula error and I think it is my syntax. I am able to use the TEXT()
command in a SUMPRODUCT formula and it works but I prefer SUMIFS. Can this be
done with SUMIFS?

The comparison should not be with sumproduct but with sumif, you cannot
enclose a range in sumif or countif with another function.


Btw, there is no need to use the ampersand in this case,

,AJ6


will do

--


Regards,


Peo Sjoblom


"MurrayBarn" wrote in message
...
I have two sheets. The one called CorpClaims and one called Summary, the
latter is where I want the answer. On CorpClaims is a list of data with
various headings such as DateofLoss, Amount, DescriptionofLoss etc

On Summary is a small table that has Description of Loss down the left in
Column A, in row 2 is the applicable month (the input would be 01/08/2009
for
August 09 and formatted as Aug 09). Then the value part of the table would
be
the sum of all amounts that fall under the Description of Loss, (which is
defined in column AJ) that fall in a certain month.

=SUMIFS(CorpClaims!L2:L20000,CorpClaims!F2:F20000, "="&AJ6,text(CorpClaims!I2:I20000,"mmyyyy"),"="&te xt(B2,"mmyyyy"))

The formula works well until you add the second criteria, then I get a
formula error and I think it is my syntax. I am able to use the TEXT()
command in a SUMPRODUCT formula and it works but I prefer SUMIFS. Can this
be
done with SUMIFS?

Except that you can specify operators = < < along with the criteria you
cannot format the values or specify conditions within SUMIF()

If this post helps click Yes
---------------
Jacob Skaria


"MurrayBarn" wrote:

I have two sheets. The one called CorpClaims and one called Summary, the
latter is where I want the answer. On CorpClaims is a list of data with
various headings such as DateofLoss, Amount, DescriptionofLoss etc

On Summary is a small table that has Description of Loss down the left in
Column A, in row 2 is the applicable month (the input would be 01/08/2009 for
August 09 and formatted as Aug 09). Then the value part of the table would be
the sum of all amounts that fall under the Description of Loss, (which is
defined in column AJ) that fall in a certain month.

=SUMIFS(CorpClaims!L2:L20000,CorpClaims!F2:F20000, "="&AJ6,text(CorpClaims!I2:I20000,"mmyyyy"),"="&te xt(B2,"mmyyyy"))

The formula works well until you add the second criteria, then I get a
formula error and I think it is my syntax. I am able to use the TEXT()
command in a SUMPRODUCT formula and it works but I prefer SUMIFS. Can this be
done with SUMIFS?