SUM IF ARRAY FUNCTION two conditions - not always working ?

LORDMARKS

New Member
Joined
Jun 5, 2014
Messages
39
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?

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
:)
 
Upvote 0
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 :biggrin:
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top