Array formula weird result

[Solerman Kaplon via OfficeKB.com]

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=T125))=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

 

[Frank Kabel]

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

 

[Guest]

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


Regards,

Peo Sjoblom