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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

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:
 

Watch MrExcel Video

Forum statistics

Threads
1,108,911
Messages
5,525,588
Members
409,652
Latest member
strangelyangely

This Week's Hot Topics

Top