array or IF formula problem (?)

  • Thread starter Thread starter Mark W
  • Start date Start date
M

Mark W

I'm having problems with the following formula:

=IF(A2=$C$2:$C$438,B2-$D$2:$D$438)

It works fine until the values in column C are not in the
identical row as column A. The way I read it, it should
find if the value of the specified row in column A is
anywhere between C2 and C438 then the value in the
corresponding row of D2:D438 should be subtracted from B.

What am I doing wrong? A sample of the spreadsheet and the
problem I'm having is below.

c00042230.xml 10/30/2003 14:20 c00042230.xml
10/3/2003 10:06 27.17599537
c00042231.xml 10/30/2003 15:16 c00042231.xml
10/3/2003 10:06 27.21496528
c00042232.xml 10/30/2003 14:26 c00042232.xml
10/3/2003 10:07 27.17987269
c00042587.xml 10/29/2003 13:14 c00042291.pdf
10/3/2003 15:18 FALSE
c00042588.xml 10/29/2003 13:15 c00042301.pdf
10/3/2003 15:54 FALSE
c00042589.xml 10/29/2003 13:31 c00042587.xml
10/7/2003 12:04 FALSE
c00042590.xml 10/29/2003 13:31 c00042588.xml
10/7/2003 12:04 FALSE
 
Mark

One way:

=SUMPRODUCT((A2=$C$2:$C$438)*(B2-$D$2:$D$438))

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.
 
Back
Top