excelexcel-formulasumproduct
Excel Use of SUMPRODUCT to multiply column then reduce percentage column
Update: I've got the formula to work but now get a warning that the formula in the cell refers to cells that are currently empty, can someone help with that? The formula now is:
=SUMPRODUCT(A1:A2,B1:B2,1-C1:C4%)
I'm sure this is a simple one, but I can't fathom it
I'm trying to multiply the rows of one column, with the corresponding values in another. That part I've got, what I need to do now is reduce this value by the percentage in another column.
Here's the dataset
| Column A | Column B | Column C |
|---|---|---|
| 10 | 5 | 10 |
| 5 | 6 | 8 |
| Total |
What I have so far for the Total is this formula:
=SUMPRODUCT(A1:A2,B1:B2)*1-C1:C4
Which isn't working
COLUMN C is the percentage column
The total is the value of the formula I'm trying to create
Hope this makes sense
Solution
Put the column C range inside the SUMPRODUCT:
=SUMPRODUCT(A1:A2,B1:B2,(1-C1:C2))
- ODBC error when trying to connect MS Excel to SPSS file on fileserver
- No definition found for GetActiveObject from System.Runtime.InteropServices.Marshal C#
- In Excel, create table showing the row,column of the cells in another table
- How to connect to MS Dataverse using ADODB Connections in VBA
- How to reliably increment a Character+Numeric value in excel
- REDUCE with conditional VSTACK inside LAMBDA. Is it possible?
- In a standard MS Access SQL query output that does not have any aliases, how do I replace the full names by their "first-letters" aliases?
- Incorrect comparison result with calculated date
- How to ignore empty values in a column in Excel VBA?
- Looping through rows in Excel to assign a counter to different rows
- SUMPRODUCT with TEXTSPLIT and MATCH formula returning #VALUE! error when summing multiple intersections in Excel
- Add new button to worksheet with Excel Add-in
- Using VBA in Excel to Google Search in IE and return the hyperlink of the first result
- How to open excel file fast in Python?
- List all values of overlapping dates
- Using a variable to set a userform object name
- Remove 'The ' from the beginning of a book title
- How to enter information in one cell based on text in another cell
- How to create a Excel formula for converting Cell value to Hexadecimal number?
- retrieving data from table but getting "compile error: expected ="
- Update open read-only version of shared workbook to show saved changes made on editable version of workbook
- How to show current user name in a cell?
- Which Regex replacements help when rewriting MS Access SQL queries as mere TSQL queries? How can these be looped over with Excel as input and output?
- Filter for values that are <0.5 and delete visible rows
- FileSystemObject to collect video file length
- Excel VBA splitting a string into a Date
- Finding a value in an Excel array
- Delete rows that are part of merged cells
- Remove URL and keep file name with extension
- Excel VBA - Nested Data in XML file