I am at a loss. I have entered the following formula into Rows 2 and 3

Row 2 =SUM(IF($A$2:$A$135=1,IF($D$2:$D$135="XA",$H$2:$H$ 135,0),0))
Row 3 =SUM(IF($A$2:$A$135=1,IF($D$2:$D$135="SL",$H$2:$H$ 135,0),0))

Column A refers to the Quarter (1, 2, 3, 4), Column D refers to the product and column H is the cost. I want the total cost entered if A and D meet the criteria.

This formula works fine with column D as "XA", and it works fine with "SL" as long as column A is 1, 3 or 4. However it will not work if I change column A to 2 and column D to "SL"

I have no idea why. I have had others look at this and there is no explanation as to why it will not work. I have tried using a new workbook, new worksheet, reentered the formula from scratch, etc. Still will not work. Any help will be appreciated.

Hi Lee,

Not easy to say why it's not working for that one set of conditions without seeing the actual workbook.

Which version of Excel are you working in?

I suspect that SUMPRODUCT would be easier to work with if you're using 2003 or previous or SUMIFS if 2007 or later...

Hi Lee,

Am Wed, 18 Jul 2012 18:44:01 +0000 schrieb Lee Andley:

I am at a loss. I have entered the following formula into Rows 2 and 3

Row 2 =SUM(IF($A$2:$A$135=1,IF($D$2:$D$135="XA",$H$2:$H$ 135,0),0))
Row 3 =SUM(IF($A$2:$A$135=1,IF($D$2:$D$135="SL",$H$2:$H$ 135,0),0))

Column A refers to the Quarter (1, 2, 3, 4), Column D refers to the
product and column H is the cost. I want the total cost entered if A
and D meet the criteria.

what Excel version do you use?
xl2007 or later:
=SUMIFS(H:H,A:A,1,D:D,"XA")
xl2003 or earlier:
=SUMPRODUCT(--(A1:A135=1),--(D1:D135="XA"),H1:H135)


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2