SUM IF ARRAY FUNCTION two conditions - not always working ?

[LORDMARKS]

Hi All

the below function is used in a table of many similar functions, The highlighted C is the main value changed to return the desired value. This works fine in all my calculations except when i exchange the C for a D. I have looked at the source data and anything else I can think of, but I cant explain why I get N/A# on that variable??

{=SUM(IF((Archive!$C$33:$C$6000=$A104)*(Archive!$B$33:$B$6000=$G$3),Archive!$J$33:$J$6000))}


Thanks if you can shed any light on this

 

[Andrew Poulsom]

Any #N/A errors in D33:D6000? Can't you use SUMIFS?

 

[LORDMARKS]

Any #N/A errors in D33:D6000? Can't you use SUMIFS?

sadly no, As most of the people At my work are still on office 97, there is no SUMIFS function built in, so this was the only way I could find to get around the issue.

To make my concern clearer:

{=SUM(IF((Archive!$C$33:$C$6000=$A104)*(Archive!$B$33:$B$6000=$G$3),Archive!$J$33:$J$6000))} - works fine
{=SUM(IF((Archive!$D$33:$D$6000=$A104)*(Archive!$B$33:$B$6000=$G$3),Archive!$J$33:$J$6000))} - WILL NOT WORK ???
{=SUM(IF((Archive!$E$33:$E$6000=$A104)*(Archive!$B$33:$B$6000=$G$3),Archive!$J$33:$J$6000))} - works fine

All I can think is that it must not like the values it is looking at, else I would just get 0 and not N/A

Thanks as always, this is normally my first and last point of call

 

[Andrew Poulsom]

You will get #N/A if one of the cells in D33:D6000 contains #N/A. What does this return?

=SUM(Archive!$D$33:$D$6000)

 

[LORDMARKS]

You will get #N/A if one of the cells in D33:D6000 contains #N/A. What does this return?

=SUM(Archive!$D$33:$D$6000)

That was perfect thanks... an old bit of formula had been pasted in form earlier code at row 5000+. Deleted the rows and all work perfectly.

Just got to work out the rest of my problems now.

Thanks again