"The definition of insanity is doing the same thing over and over and expecting different results." - (Benjamin Franklin)
|Tired of making the same reports every day ?
|Want your job get done effectively and efficiently ?
|.... Contact us immediately at firstname.lastname@example.org
SUMPRODUCT This is an excerpt from Pierre Leclerc (www.excel-vba.com)
SUMPRODUCT is the most powerful and simple function in Excel.
Here are a few examples of what can be done with SUMPRODUCT
Note: SUMPRODUCT was not originally designed to solve the problems below so you cannot call SUMPRODUCT
using the function icon or from the menu bar "Insert/Function" to solve these problems.
Here is a typical set of sales data. But imagine a set of accounting data, manufacturing data, planning data....there can be 65,000 lines and 256 columns in the table and the order of the data in unimportant, SUMPRODUCT will do the job.
With a SUMIF formula you can find the total sales for New York =SUMIF(N1:N7,"New York",P1:P7) but with SUMPRODUCT:
With SUMPRODUCT you can extract the price of a car from the list above based on the make AND the model AND the year. No other formula can do that. So on an invoice or quotation spreadsheet you enter the make, the model and the year and a SUMPRODUCT formula will find the price for you automatically. No manual entry, no mistake. You can lookup in any table with 1, 2, 3 or any number of criteria with SUMPRODUCT.
You have a list of 3,000 names of people with the State they reside in and their annual salary. You need a simple formula that will extract a tax rate from a table like
the one above. What is the rate for a New York resident who earns $59,254? What is the rate for an Oregon resident who earns $125,654?
site by Affordable Solutions