Formula help needed

[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 th
product and column H is the cost. I want the total cost entered if
and D meet the criteria.

This formula works fine with column D as "XA", and it works fine wit
"SL" as long as column A is 1, 3 or 4. However it will not work if
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 n
explanation as to why it will not work. I have tried using a ne
workbook, new worksheet, reentered the formula from scratch, etc. Stil
will not work. Any help will be appreciated

 

[Claus Busch]

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,"XA")
xl2003 or earlier:
=SUMPRODUCT(--(A1:A135=1),--(D1135="XA"),H1:H135)


Regards
Claus Busch