SUMIF and MULTIPLE DATA
 

I have a spreadsheet with about 6 columns and 25 rows of data. One of the
columns represents dollar amounts...and the remaining columns represent
account names, account numbers...etc.
I'm trying to come up with a way to sum all the dollar amounts if the data
in the other columns is the same.
I've used the sumif formula when I have only one piece of data I'm looking
for in common, but how can I get the same result when I'm looking for
information with at least two columns of the same data?
I've tried used the sumproduct formula.
=SUMPRODUCT(($B$106:$B$505="*042000314*")*($D$106: $D$505="*637185059*")*($L$106:$L$500))
However I keep getting a #N/A result.
Any idea why this isn't working?

Hi!

Are the asterisks wildcards?

Sumproduct won't accept wildcards. Also, your last array
is not the same size. Typo?

Biff

-----Original Message-----
I have a spreadsheet with about 6 columns and 25 rows of
data. One of the
columns represents dollar amounts...and the remaining
columns represent
account names, account numbers...etc.
I'm trying to come up with a way to sum all the dollar
amounts if the data
in the other columns is the same.
I've used the sumif formula when I have only one piece of
data I'm looking
for in common, but how can I get the same result when I'm
looking for
information with at least two columns of the same data?
I've tried used the sumproduct formula.
=SUMPRODUCT(($B$106:$B$505="*042000314*")*
($D$106:$D$505="*637185059*")*($L$106:$L$500))
However I keep getting a #N/A result.
Any idea why this isn't working?
.

Brian

The easiest approach is the use an array formula. These can be used in
either of two ways: to produce an array of outputs (many of the statistical
functions do this), or to process an array of inputs.

The array formula you want for the example below would be:
=sum(if($B$106:$B$505="*042000314*",if($D$106:$D$5 05="*637185059*",$L$106:$L$505,0),0))
After you have typed this in, press <CTRL-SHIFT-Enter (hold down the
Control and Shift keys while pressing enter). Braces ( { } ) will appear
around the formula.

When you use array formulas, be sure that all of the arrays refer to exactly
the same number of rows and columns. One reason you might have been having
a problem with the formula in your message is that the last range only
extends to L500, while the first two extent to Row 505.

Good luck!

David




"Brian" wrote in message
...
I have a spreadsheet with about 6 columns and 25 rows of data. One of the
columns represents dollar amounts...and the remaining columns represent
account names, account numbers...etc.
I'm trying to come up with a way to sum all the dollar amounts if the data
in the other columns is the same.
I've used the sumif formula when I have only one piece of data I'm looking
for in common, but how can I get the same result when I'm looking for
information with at least two columns of the same data?
I've tried used the sumproduct formula.
=SUMPRODUCT(($B$106:$B$505="*042000314*")*($D$106: $D$505="*637185059*")*($L$106:$L$500))
However I keep getting a #N/A result.
Any idea why this isn't working?