Array formula weird result
 

Hi,

I have an spreadsheet likes this:

customer product category value
A X Y 10
B Z Y 20
A I 30

and a summary like this

customer categoryY others
A 10 30
B 20 0

This is a minimalist example of how my data is compound, for one of the summary columns, I have a formula like this:

=SUM(IF((NOT(ISEMPTY($H$5:$H$121))+($G$5:$G$121=T1 25))=2;$AE$125:$AE$139;0))

Problem is, there is some lines that I get #N/D, but there is none #N/D in none of the referenced cells. Now, what is weird, take B as example of where #N/D occurs, now I change one of B to Ba, and the column get a value, even if there isn't a customer with that name. Someone has any idea?

Thanks in advance

--
Message posted via http://www.officekb.com

Hi
you have different ranges. Maybe try:
=SUM(IF((NOT(ISEMPTY($H$5:$H$121))+($G$5:$G$121=T1 25))=2;$AE$5:$AE$121;0))

--
Regards
Frank Kabel
Frankfurt, Germany
"Solerman Kaplon via OfficeKB.com" schrieb im
Newsbeitrag ...
Hi,

I have an spreadsheet likes this:

customer product category value
A X Y 10
B Z Y 20
A I 30

and a summary like this

customer categoryY others
A 10 30
B 20 0

This is a minimalist example of how my data is compound, for one of the
summary columns, I have a formula like this:

=SUM(IF((NOT(ISEMPTY($H$5:$H$121))+($G$5:$G$121=T1 25))=2;$AE$125:$AE$139;0))

Problem is, there is some lines that I get #N/D, but there is none #N/D in
none of the referenced cells. Now, what is weird, take B as example of
where #N/D occurs, now I change one of B to Ba, and the column get a
value, even if there isn't a customer with that name. Someone has any
idea?

Thanks in advance

--
Message posted via http://www.officekb.com

The arrays should be of the same size, that is why you get the errors


Regards,

Peo Sjoblom

"Solerman Kaplon via OfficeKB.com" wrote:

Hi,

I have an spreadsheet likes this:

customer product category value
A X Y 10
B Z Y 20
A I 30

and a summary like this

customer categoryY others
A 10 30
B 20 0

This is a minimalist example of how my data is compound, for one of the summary columns, I have a formula like this:

=SUM(IF((NOT(ISEMPTY($H$5:$H$121))+($G$5:$G$121=T1 25))=2;$AE$125:$AE$139;0))

Problem is, there is some lines that I get #N/D, but there is none #N/D in none of the referenced cells. Now, what is weird, take B as example of where #N/D occurs, now I change one of B to Ba, and the column get a value, even if there isn't a customer with that name. Someone has any idea?

Thanks in advance

--
Message posted via http://www.officekb.com