Array Formla

[Don Rountree]

I have three named ranges on my worksheet: customer, dollars and invoice. I want to sum the dollars of each invoice by invoice and their associated credits and payment documents within each customer. I know this takes an array formula, but I can't get anything to work. Any help would be appreciated. Thanks...

Don Rountree

 

[Peo Sjoblom]

Take a look at sumproduct

=SUMPRODUCT((Customer="customer_name")*(Invoice=invoice#),Dollars)

However a pivot table might be better in this case

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

--

Regards,

Peo Sjoblom

I have three named ranges on my worksheet: customer, dollars and invoice.

I want to sum the dollars of each invoice by invoice and their associated
credits and payment documents within each customer. I know this takes an
array formula, but I can't get anything to work. Any help would be
appreciated. Thanks...

 

[Jason Morin]

Depending upon the layout of your data, it sounds like
Pivot Table would get the job done.

HTH
Jason
Atlanta, GA

-----Original Message-----
I have three named ranges on my worksheet: customer,

dollars and invoice. I want to sum the dollars of each
invoice by invoice and their associated credits and
payment documents within each customer. I know this takes
an array formula, but I can't get anything to work. Any
help would be appreciated. Thanks...

 

[Don Rountree]

I tried your suggestion - {=SUMPRODUCT((customer=C12)*(invoice=L12),dollars)} and it returned #N/A. I know it is something stupid I'm doing, but I can't figure it out. I'm compiling these numbers on an A/R report for my sales people and will be printing a .pdf report from the results, so a pivot table would not help in this situation. Thanks for any help.

Don Rountree

 

[Peo Sjoblom]

I believe the only time a sumproduct formula will return #N/A is when the
ranges are not the same
dimensions like if the customer range was for instance A2:A200 and the
invoice range was B2:B150 or something,
otherwise it would return zero if it couldn't find anything. Are your named
ranges equal in size?

--

Regards,

Peo Sjoblom

I tried your suggestion -

{=SUMPRODUCT((customer=C12)*(invoice=L12),dollars)} and it returned #N/A. I
know it is something stupid I'm doing, but I can't figure it out. I'm
compiling these numbers on an A/R report for my sales people and will be
printing a .pdf report from the results, so a pivot table would not help in
this situation. Thanks for any help.

 

[Paul]

Doesn't it return #VALUE! in that case? It returns #N/A if #N/A appears
anywhere in any of the ranges.

 

[Peo Sjoblom]

No, it returns #N/A in both (appears in range or size of range)cases. I
believe it returns value error if a range contains #VALUE or if the range
that is totaled has text or null strings in it.

 

[Don Rountree]

You were right. My ranges were not equal. Now the formula works fine. Thanks to all.

 

[Aladin Akyurek]

No, it returns #N/A in both (appears in range or size of range)cases. I
believe it returns value error if a range contains #VALUE or if the range
that is totaled has text or null strings in it.

Text values in the range to sum cannot thwart a SumProduct formula if the
formula adheres to the comma syntax:

Sumproduct(Conditional*Conditional*...,RangeToSum)

[...]