# SUM IF ARRAY FUNCTION two conditions - not always working ?

#### LORDMARKS

##### New Member
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

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
Any #N/A errors in D33:D6000? Can't you use SUMIFS?

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

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)

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

Replies
4
Views
364
Replies
6
Views
207
Replies
15
Views
720
Replies
4
Views
787
Replies
7
Views
352

1,211,679
Messages
6,103,245
Members
447,850
Latest member
thebuzzman15

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back