Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? . |
#3
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use the sumif if I have multiple criteria (i.e. greater . | Excel Worksheet Functions | |||
How do I sum (like sumif) but predicated on multiple criteria, in. | Excel Worksheet Functions | |||
How do I ask for multiple criteria when creating a "sumif" formul. | Excel Worksheet Functions | |||
sumif to add data in multiple sheets | Excel Worksheet Functions | |||
Is it possible to specify multiple condition with SUMIF? | Excel Worksheet Functions |