# SUMPRODUCT Formula Throwing Out #VALUES!

Jun 9, 2009fix cell E8-E19 (totals). I don't think its anything to do with the date format.

View 2 Repliesfix cell E8-E19 (totals). I don't think its anything to do with the date format.

View 2 RepliesADVERTISEMENT

I am trying to sum up column V, when for a specific date in column z, and only sum when the two names EEM, Merri1* are involved. I used the * because there are a couple of variations for MErri1 after the 1. The formula is not working. I believe it's the way I'm combining this

[Code] .....

Full formula is below:

[Code] .....

I've used the sumproduct formula very sucessfully in a workbook. The workbook is used to monitor discrepancies routed to other departments. Column U has the status of the discrepancy (Open, Closed, Cancelled etc). The below formula returns the number of discrepancies raised to a particular department. Now I need to tweak the formula to exclude values "Cancelled" found in range $U$119:$U:417.

=SUMPRODUCT(--(Register!$I$119:$I$417=$A4),--(Register!$C$119:$C$417=B$2),--(Register!$B$119:$B$417))

Question is...in the below formula the red are pointing back to name ranges in a different tab on my spreadsheet. On that spreadsheet I also have a name range called DTCCSUBSUPPREF

I would like to add within my formula, a string which will point to that name range and exclude values if they fit my criteria. Basically I need to see values only where comp or tri dont show up. comp or tri could be the only value in the cell, or could be in the beginning of a string, at end, or in between. Below examples. I have tried (--(DTCCSUBSUPPREF<>"*comp*")*(--(DTCCSUBSUPPREF<>"*tri*") , and I would have thought the * characters would have served as a catch all.

Below is the entire forumla just in case.

11111111FT04comp

comp

tri

11comp

11tri

tri11111111FRP06

=SUMPRODUCT((--(DTCCSUBTRANSTYPE<>$G$2))*(--(DTCCSUBEVENTTYPE<>$I$2)*(--(DTCCSUBBACKLOAD<>$I$3)

*(--(DTCCSUBINPUTSOURCE<>$I$4)*(--(TEXT(DTCCSUBTRADEDATE,"MMM-YY")=$D$2))))))

Need the formula to multiply monthly values with a maximum value in any one period? The sample file attached explains it better.

View 2 Replies View RelatedI attempted to initialize values on a few Dim statements, but got compile errors: "expected end of sentence".

VB:

Dim sumTtl As Currency = 0

Dim searchTxt = "West"

Basically I want the macro to look through a column of values and if any cell in that column has the value 'LOCK' the macro would hardcode 'n' into the adjacent cell. Additionally, because the column in which the LOCK cells would appear is part of a vlookup, it returns #N/A which I would like my macro to ignore and then continue to evaluate the next cell all the way to the end of the column.

The code is the following:

Sub Hard_Code_Cells()

On Error Resume Next

lastrow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:= _

xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

[Code] .......

I've been receiving the error message 400 when I trigger the macro and I'm not sure how to correct my code to resolve the issue.

I have some code that is working great except that it throws up an error on the last run through because it doesn't find any more rows to delete. I've tried "On Error GoTo" but it doesn't catch it. It seemed to work on my home computer which is running Excel 2010 but then on Excel 2007 I get the debug screen and I don't want that to show up for other users that I share the code with.

Sub DeleteHeaders()

Dim lastrow As Integer

lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row

[Code]....

I am currently working on a project which at various times does 2 different things. These things are to be done on a "PROTECTED" (UserInterfaceOnly:=True ) worksheet.

The first is a Range.Replace command.

The other is a Hyperlinks.Add command.

Now when I'm testing ( typically the worksheet is not protected ) everything works like a champ. But as soon as I protect the sheet the following scenario happens.

1. The replace DOES NOT work.

2. The hypelink.add DOES NOT work.

when the sheet is protected it ends up throwing a runtime error ( 1004 ) "Protected Sheet error" the real odd thing about this is that it doesn't throw the error on the command itself. On the hyperlink the error happens about 3 command further along in the flow............

I am trying to add values

and I am having a problem when I introduce a blank lookup as in status here.

I have included the formula below ...

i have information across three columns the first has user-names in each row the whole way down, the second has between 1-7 activity codes (when not eacher user will use), the third has the times they have been on these codes.

what im trying to do is match the name, code and get the time to be displayed in a fix table, as the reported information is not always in the same structer

eg

user1 code 1 0:02:00

user1

user1

user2 code 3 0:05:00

user2 code 6 0:20:00

user2

now i've got it in my head that sumproduct iwll be the best way to get it, but i cant seam to get the third array to work properly, and always comes up with either value or NA

I'm trying to use SUMPRODUCT as a sort of SUMIF/VLOOKUP hybrid. The following formula:

Code:

=ROUND(SUMPRODUCT('[12 GB BUDGET 15 V1 LOCAL VERSION.xlsx]

Revenue'!$G$3:$FM$3=$K2)*('[12 GB BUDGET 15 V1

LOCAL VERSION.xlsx]Revenue'!$G$27:$FM$27);0)

... should check for the value in cell K2 of the current sheet, compare it to all the values in row 3 of the Revenue sheet, and add up any and all values for those columns in row 27 where there's a match in row 3.

I don't get an error, but it's not adding anything up, either.

Is there a way to sum a list that contains both text and values using the SUMPRODUCT function? My efforts yielded the #VALUE! error. SUM and SUMIF will ignore the text but I have multiple criteria.

View 9 Replies View RelatedThe problem facing by me that I have a worksheet in which I count some values through sumproduct function in vba but its not working but if i manually put in this in sheet it works.here is the code.

Dim Sal As Workbook

Dim rng As Range

Dim rng1 As Range

Dim Dept As Range

Dim Dept1 As Range

Dim rg As Range

Dim i As Byte

Sub salries()

Application.DisplayAlerts = False

On Error Resume Next

Set Con = Workbooks("Branch Wise Deparment Wise No. of Staff.xls")

Set Sal = Workbooks("salarysheet.xls")

Sal.Activate

Sheets("Working").Delete

Sheets("GT").Activate

Range("B3").Select

Set rng = Range(ActiveCell, Selection.End(xlDown))..............

I'm working on a spreadsheet to rank stores based on how they perform in certain metrics. These metrics are weighted, and occasionally a metric for a store will get waived. I'm having trouble figuring out how to handle this without making a custom formula for each occurrence.

View 7 Replies View RelatedIs there a simple way to include the values of an entire range?

This is what I tried, but I get #name

Code:

=SUMPRODUCT((Datasheet!J2:J65000 = Range("C3:AA3"))*Datasheet!F2:F65000)

If I use a single cell it works, but only with what matches that cell.

Code:

=SUMPRODUCT((Datasheet!J2:J65000 = C3)*Datasheet!F2:F65000)

I'd like the criteria to match anything in that range C3:AA3. Do I need to specify each cell individually or is there an easy way have it use the entire range's values for its criteria?

I am trying to sum up the rows that have multiple values in one column.

Here is my curent formula THAT works

=SUMPRODUCT(($H$46:$H$5787="EO-Deal Processing-Closing")*($K$46:$K$5787="Submitted")*($I$46:$I$5787="2-medium"))

Now I also want to add the following

($K$46:$K$5787="Assigned")

How can I get the value I need so that column "K" I get returned both "submitted" and "assigned"?

Sumproduct formula with selection criteria of "A", "B"... in the first column and numeric values in the next colum. The selection is controled by a List where the user can choose "A", "B", ... ,or "ALL". What wildcard-type (pseudo) is needed to select all values when "ALL" is chosen?

I'm using Sumproduct because there is other selection criteria, but it should not impact this part of the formula.

Example: Sumproduct((A1:A100=X1)*(B1:B100)) , where A=selection aray, B=numeric value, X1=corresponding list selection to A

I am attempting to count from a spreadsheet the reference number of a customer (numbers and text) based on two criteria.

1, If column G= Requested

2, Column I = Meeting

Count Row E

I thought a sumproduct was best and have started using it for the first time, I thought this should work but I keep getting a #NUM! error.

I have tried with numbers and it works but the non numeric aspect is difficult.

[Code].....

I'm looking at the formula in Sheet3, cell F5. You'll see that the criteria "--(Sheet2!I:I<>Sheet1!O:O)" does not seem to be working. I need the formula to not include any values from Sheet2!E:E, where the corresponding value in column I does not equal any value in Sheet1!O:O. Also, these values can change by user so its not as simple as just typing them in to the formula as an array...

That VBA part of this is that it's in a macro, so if there is any way to make this easier using code instead of a simple formula.

The Table : Column R represents a list of services

Column L is the full price

Column N is the discounted price ( my example reflects no discount in this case)

Column C is an associated code , in some cases the code is n/a Starting at Row 13 ........

I'm trying to write a SUMPRODUCT formula (cell H2 in the attachment) that gives different outputs according to the value of another cell (H1), but the output values are incorrect. I suppose it's because the "else" value given in the IF formula won't be recognized as a formula but as a text.

View 5 Replies View RelatedI'm currently using a series of SUMPRODUCT formulas to populate a summary page from a large data source. This works fine in the most part, however one part of the summary page calls for a number of different criteria to be counted within the same data range. I'm currently using a work around by having three seperate SUMPRODUCT formulas on hidden rows, and then totaling their answers to give me the result I want, however I wondered if there was a cleaner and better way to do this? An exampel of the formula I am currently working with is below:

=SUMPRODUCT(('Filtered Data'!$A$2:$A$10000="Stock-Out")*('Filtered Data'!$I$2:$I$10000="Open")*('Filtered Data'!$Q$2:$Q$10000="A"))

The final part of the formula is what is causing me the problem - I would like it to look for and count occurances of "A" , "B" , & "C"

I have 3 ranges of data which each have a quantity and a length. I want to create from these ranges a list of unique values with the total quantity required of each value (as shown). Inputs on any column may be blank but where there is a length, there will be an adjacent quantity to the left.

I'm using Excel 2010.

I've got a workbook where sheet "Raw Data" is used to enter audit findings. Subsequent sheet "Analysis" contains formulas to extract quantities and nature of audit findings so that they can be shown on quarterly reports.

When I set up the workbook the formulas on the "Analysis" sheet worked fine. The department has filled the columns with data and now all the formulas are returning #Value! There's something fishy going on here.....

=SUMPRODUCT(--('Raw Data'!$H$7:$H$1000=A4),--('Raw Data'!$K$7:$K$1000=C$1))

No doubt due to a fundamental lack of understanding my SUMPRODUCT formulae (please see attached workbook) is not working.

Can someone be kind enough to point out the error of my ways? I've tried every possible combination (except the right one).

I have a function that has a formula sumproduct and here it is:

Set RSheet = Worksheets("Rawdata")

Set RSheet = Worksheets("Rawdata")

RSheet_lastRow = RSheet.Cells(Rows.Count, "A").End(xlUp).Row

RSheet.Range("AJ2:AJ" & RSheet_lastRow).Formulaarray = "=SUMPRODUCT

((V2>=ProjectedStarts!$K$1:$K$45)*(V2<=ProjectedStarts!$L$1:$L$45),ProjectedStarts!$M$1:$M$45)"

The problem is that when the formula is sent to each cell V2 (bolded) does not change to V3, V4, V5.

a1=good

a2 = 100

b1 = bad

b2 = 50

c1 = good

c2 = 100

would like to sum a2+b2+c2 only if a1,b1,c1 = good.

I'm using Excel 2003. I have been using SUMPRODUCT to look at too compare columns and data that is contained in both. Column L11:L52 contains the numbers 1 to 4 and Column K11:K52 contains the word YES in some cells. I just want to look and see if text is contained in a row in column L is it contained in that row in column K. This is the formula I'm using but it seems over complicated and I'm sure there is a way of just looking if the cell contains information without worrying what it is.

=SUMPRODUCT(--(Data!$L$11:$L$52=1),--(Data!$K$11:$K$52="YES"))+SUMPRODUCT(--(Data!$L$11:$L$52=2),--(Data!$K$11:$K$52="YES"))+SUMPRODUCT(--(Data!$L$11:$L$52=3),--(Data!$K$11:$K$52="YES"))+SUMPRODUCT(--(Data!$L$11:$L$52=4),--(Data!$K$11:$K$52="YES"))

I am trying to sum the same data across several tabs but I am using information in the column and row headers to find it. For some reason this formula wont work and I can't figure out what I'm doing wrong.

=SUMPRODUCT(N(INDIRECT(""&Sheets&"!"&ADDRESS(MATCH($A$12,$A:$A,0),MATCH($G$3,3:3,0)),FALSE)))

I'm getting #REF! and when I step through it, it fails when it reaches the Indirect statement.

Copyrights 2005-15 www.BigResource.com, All rights reserved