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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

LORDMARKS

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jun 5, 2014
Messages
39
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:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,980
Members
430,100
Latest member
namhnz

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
Top