Array Formla

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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


Don Rountree said:
I have three named ranges on my worksheet: customer, dollars and invoice.
Click to expand...
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...
 
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,
Click to expand...
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...
 
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
 
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


Don Rountree said:
I tried your suggestion -
Click to expand...
{=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.
 
Doesn't it return #VALUE! in that case? It returns #N/A if #N/A appears
anywhere in any of the ranges.
 
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.
 
Peo Sjoblom said:
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.
Click to expand...

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

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

[...]
 
Back
Top