2003 function moved to 2007 unable to change absolute references
 

Hi,

I have this function =SUM(IF($D$25:$D$993="x",IF($B$25:$B$976=""&A3,1), 0))
from excel 2003 and now have converted the spreadsheet to 2007.

I have inserted rows and now need to change $D$25 and $b$25 to reference row
22.

Or is there a better way to do this. count number of x's from d22:d900 if
b22:b900 equals a3.

2003 function moved to 2007 unable to change absolute references
 

You are not able to edit the formula? Is it working in 2003?

It is probably because one range ends at 993 and the other at 976... size of
both ranges should be same...

I prefer to use SUMPRODUCT in these cases like this
=SUMPRODUCT(--($D$22:$D$993="x"),--($B$22:$B$993=A3))
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"Sivart" wrote:

Hi,

I have this function =SUM(IF($D$25:$D$993="x",IF($B$25:$B$976=""&A3,1), 0))
from excel 2003 and now have converted the spreadsheet to 2007.

I have inserted rows and now need to change $D$25 and $b$25 to reference row
22.

Or is there a better way to do this. count number of x's from d22:d900 if
b22:b900 equals a3.