Sumproduct Trouble

Jun 5, 2009

I am a Project Manager, Surveyor involved in new ships building. I have to control and report the budget and actual hours every month and at the end of the project. I need to calculate the sum of the actually spent hours for the inspections done every month and send them to our head office. I use SUMPRODUCT function but Excel displays #VALUE! error eventhough there are no cells used in the formula which contain text!

The formula in E11 = SUMPRODUCT(--(MONTH(E2:E8)=MONTH(E10)),--(YEAR(E2:E8)=YEAR(E10)),H2:H8). E2:E8 and E10 are formated as a date. I tested all of them with ISNUMBER and the result is TRUE!!! Is there other formula I can use for the intended purpose?..............

View 4 Replies


ADVERTISEMENT

Formula Trouble In VBA

Aug 9, 2009

I'm getting a compile error before I even get a chance to use it. I'm sure it's simple but it's got me stumped.

View 3 Replies View Related

Sumif Trouble

Apr 20, 2009

why the attached formulas ("Datasum" tab E2:AB4) are not working properly? I am not looking for a cumulative total (which I appear to be getting) but rather just the number of fruit requests by hour.

View 4 Replies View Related

Should Be Easy, Having Trouble

May 18, 2006

I'm having real trouble selecting a series of rows based on variables for the purpose of cutting them and pasting them elsewhere in the spreadsheet. For example, if this is my spreadsheet

row
2 I Hate
3 Bill
4 O'Reilly

I want my code to select the range of 2:4, cut them, and paste them elsewhere but I want 2:4 to be variables. For example

Fun = 2
Stuff = 4

Rows(Fun:Stuff).Select
Selection.Cut

But doing "Rows(Fun:Stuff).Select" does not work at all. If you do Rows(Fun).Select, this works to select Row 2, but I can not use the variables to select a range.

View 2 Replies View Related

Trouble With Compiling 2 Ranges Into A Summary

Apr 23, 2009

I have looked all over the place but nothing seems to work for me.
In detail, I have a quote book i made with two sheets for different types of items on them. For example:

Sheet 1 is for wooden items, and sheet 2 is for metal items.
So after i enter all the items on sheet one and sheet two, I want to be able to combine all the items from both sheets into one summary sheet that i can send to the customer.

I tried consolidate, and messed around with pivot tabels but it didnt seem to be what i'm looking for.

View 9 Replies View Related

Having Trouble Opening An Access File Using This Code.

Feb 9, 2009

Having trouble opening an access file using this code. Using the following code

View 3 Replies View Related

Trouble Setting Focus After Closing MsgBox

Mar 31, 2008

I am using the TextBox.Change method to test for numerical input. How can I highlight incorrect input after closing the MsgBox? The following code doesn't always work:

If Not IsNumeric(TxtStage1.Value) Then
Response = MsgBox("Numerical value required", vbExclamation + vbOKOnly, "Invalid input")
TxtStage1.SetFocus
TxtStage1.SelStart = 0
TxtStage1.SelLength = Len(TxtStage1.Text)
End If

View 9 Replies View Related

Trouble With Date Format When Making One Range Equal To Another

Jun 18, 2009

I am using something like this to copy data from one workbook to another

View 2 Replies View Related

Sum & Sumproduct

Dec 18, 2008

I have the following formula's:

SUMPRODUCT(($O$9:$O$259="Active")+0)+SUMPRODUCT(($O$9:$O$259="Clearing Tmrw")+0) and

SUMPRODUCT(--($O$9:$O$259="Active"),$B$9:$B$259)+SUMPRODUCT(--($O$9:$O$259="Clearing Tmrw"),$B$9:$B$259) and

SUMPRODUCT(--(YEAR($A$9:$A$259)=$E$4)*($O$9:$O$259="Active"),$B$9:$B$259)+SUMPRODUCT(--(YEAR($A$9:$A$259)=$E$4)*($O$9:$O$259="Clearing Tmrw"),$B$9:$B$259)

My spreadsheet A1 to Q7 are where the headers and above formula's are and row 8 is hidden and has other formula's in (col C,M,N,O,P & Q)

The User then clicks the insert row macro which inserts a blank row (with row 8 formaules) into row 9.

However, the above formula's then change from 9 to 10 therefore, not picking up all the amounts.

Can anyone help me so that the formulas continue from row 9 ?

View 10 Replies View Related

Sumproduct..?

Jul 11, 2009

I have the following formula:

View 4 Replies View Related

SUMPRODUCT = #Value!

Mar 25, 2009

I have a spreadsheet in which I am using SUMPRODUCT. As you can see from the attachment, on the "Breakout by PIpeline" tab, Columns C, D & F are working perfect. However, Column B & E are duplicates of C & D - just wanting to pull the volume from the "Detail" tab instead of the dollars...and I keep getting the #VALUE! error.

I have tried formatting the Volume column differently - and even multipling the data times -1 and then pasting back in.....

View 3 Replies View Related

How To Add Another Sum To Sumproduct

Jul 11, 2012

How to add another sum to this formula?

this is the formula i have at the moment

=SUMPRODUCT(--($AA$21:$AA$6376

View 4 Replies View Related

Using SumProduct

Dec 4, 2007

Here's my question.
jan- feb- mar- apr- may
rep a 1 1 2 5 1
rep b 1 2 7 7 7
rep c 5 5 1 2 2
rep d 1 1 1 1 1
rep e 2 2 2 2 2
rep f 1 2 3 4 5
rep g 0 0 0 7 9

date date
March May

How do I set it up so that I will have two cells that will contain a month. Once the 2 months are selected it will give me the total for each rep during that time period? Do I use sumproduct?

View 9 Replies View Related

SUMPRODUCT #NAME

Aug 12, 2008

I have a report with a macro that imports a .TXT file into Excel then calculates the follwing formula. For some reason, before the .TXT file is loaded the formula is fine and has no errors, then when it is run I get a "#NAME?" error. I thought it might be because the formula changed during the macro but that's not it. Examples are below.

I've looked online and tried naming the ranges, but that doesn't work. I've tried the built in help but that didn't help. I've tried making sure the analysis pak is installed but that's not it either.

I've ran out of ideas now and you guys are my last hope. Any clues...??

Before macro:
=SUM(SUMPRODUCT(--(DelDate>=6),--(Branch=$B3),--(Status"DEAL"))-SUMPRODUCT(--(DelDate>=6),--(Branch=$B3),--(Status="AWAI")))

After macro:
=SUM(SUMPRODUCT(--(DelDate>=6),--(Branch=$B3),--(Status"DEAL"))-SUMPRODUCT(--(DelDate>=6),--(Branch=$B3),--(Status="AWAI")))

View 9 Replies View Related

SUMPRODUCT Using OR

Aug 13, 2008

=SUMPRODUCT(OR(MIS!$T$4:$T$2042>500000)*(MIS!$U$4:$U$2042>500000))*(MIS!$Z$4:$Z$2042=C2)

it is if the cell in Column T >500,000 or the cell in Column U is more than 500,000. I keep getting a value error and I can't find a way around it.

View 9 Replies View Related

Sumproduct With Min/Max ...

Aug 26, 2008

I'm working on a project on predicting future cash flows from loans we have made and have ran into a wall as i can't figure out the formula to achieve what i would like to do. I've gotten a lot of help from you guys in the past and would appreciate any help you may be able to provide for this problem. So here it goes. First, here is how i have the spreadsheet setup.....

View 9 Replies View Related

Sumproduct In VBA Getting #Value

Nov 12, 2008

I've got a spreadsheet that had _many_ sumproducts and calls to vba functions and when it recalculated took some time to finish, so I thought I'd group the vba function with the sumproduct to see if it was faster, but I keep getting a #Value! error. The sumproduct layout worked fine on the actual spreadsheet, so I'm sure something is lost in translation. Here is my
Function weight(Aref As Range, count As Integer, Bref As Range) As Variant

Dim i As Integer
Dim top As Integer
Dim vA() As Variant
Dim cA() As Variant
Dim vR As Range
Dim cR As Range

Dim count As Integer

count = Widgets.Rows.count

ReDim vA(count - 1)
ReDim cA(count - 1)


top = Aref.Rows.count

For i = 0 To top - 1.............................

View 9 Replies View Related

Sumproduct :: Get Sum Of 0

Apr 29, 2009

I seem to get a sum of 0 when I use this sumproduct syntax: SUMPRODUCT(($R$77:$R$442="H")*($Q$77:$Q$442="OTE"),(D77:D442)). What am I not doing?

Below is an e.g. of my data

CDEFGHIJKLMNOPQRAdmin4444440 ...

View 9 Replies View Related

SUMPRODUCT Getting #Value

Jul 27, 2009

=-SUMPRODUCT((Catagory=$A26)*(MONTH(Paid_Date)=12)*
(YEAR(Paid_Date)=12)*(How="Till")*(How="Banking")*(Expenses))

This problem is I keep getting a #Value! error when I place the date section into formula. I have check to see thatthe date format is correct and as far as I can tell it is EXAMPLE: 06/12/2007

This formula did work in a nother worksheet, thus the confusion Ihave

The other issue is how to get two different text items in the "HOW" group to be True for Formula to pull requested values???

View 9 Replies View Related

Sumproduct In VBA

Apr 30, 2003

Now I am trying to build a user-defined function to use it.
It works in EXCEL but I can't get it to work in VBA


Function xxx(x,y)
Dim a, b, c As Range
a = Range("a4:a14") ' contains text
b = Range("b4:b14") ' contains text
c = Range("c4:c14") ' contains values
Range("a20") = Application.SumProduct((a = x) * (b = y) * c)
End Function

View 9 Replies View Related

If And Or Sumproduct

Jun 27, 2006

If B97:158=GL AND C97:C158=0001 THEN SUM E97:E158 OTHERWISE "0.00". I can't figure out the best formula to use.

View 2 Replies View Related

Sumproduct And Vba

Jun 12, 2007

sum with criteria. The solution was: = SUMPRODUCT(($A$7:$A$41=$A45)*($E$7:$E$41))

That was great, but, i need this every week, for a report, and not always all are in the same cells, and this formula is into a code, so the code loses sense when the week change. I tried to change the formula into a code, but it's impossible for me, although i have read all the post about it and sumproduct vba code. So, every week i use the same ranges in my code. So, for example:

A7:A41 in my code is:

Range(cells(7,1), cells(LastCell,1)

because i don't finish every week in the same row of column A

A45 is

Cells(LastCell + 4, 1)

I attach the same sheet of the other day

View 8 Replies View Related

Sumproduct Given A Condition?

Apr 7, 2014

I am trying to arrive at the results in the yellow cells without needing the blue cells inbetween.

A sumproduct would be best but the 2nd row of blue cells has an if statement in it.

View 3 Replies View Related

Sumproduct With Hours

Jan 19, 2014

I am trying to do a sumproduct formula with hours and it is not calculating right.

Here is my current formula:

=SUMPRODUCT(([Productivity.xlsx]Occupancy!$A$2:$A$5000>=$C$1)*([Productivity.xlsx]Occupancy!$A$2:$A$5000<=$C$2)*([Productivity.xlsx]Occupancy!$B$2:$B$50ry00=$A4)*([Productivity.xlsx]Occupancy!$C$2:$C$5000))

My values in Column C that I am trying to get the sum of are:

1:24:49
:59:08
:58:46
1:39:19
1:10:08
1:13:15
1:04:23
1:43:04
1:23:55
1:48:11
1:42:41

The result of the sumproduct I am currently getting is 7824:00:00, however when I select the above values, Excel is showing it should be 13:09:45 in the status bar, which seems accurate.

I am not sure what I am missing to get this same number.

View 6 Replies View Related

SUMPRODUCT Using A Condition

Jun 20, 2014

I would like to create a formula in D7 that calculates the weighted average growth for products (column A) manufactured by Bob (column B). In J5 I've inserted the expected result using a SUMPRODUCT function.

How to create a formula in D7 that will calculate from the data in B6:D6 the same result as in J5. i.e. How do I make the SUMPRODUCT function only consider data in columns C and D when Bob is the manufacturer?

View 2 Replies View Related

Sumproduct Problems With #N/A

Jan 24, 2007

I am using SUMPRODUCT to check if the word "Yes" appears in two colums andcount the number of occurances.

Many of the cells in the range evaluate to error #N/A - this is unavoidable.

As a result, SUMPRODUCT in the formula

=SUMPRODUCT(--(H12:H486)="yes")*(--(Q12:Q486)="yes") evaluates to #Value.

I have tried to error trap this using

=SUMPRODUCT(--(ISTEXT(G12:G486)="yes"))*(--(ISTEXT(P12:P486)="yes"))

But this evaluates to #N/A.

View 12 Replies View Related

Sumproduct Function

Nov 23, 2007

In this file I have a team in column A, in B are the relative number of games they have played, and in C the filed goal % for them in that game. The value I am trying to return the average field goal % for that team in there last 10 games. I thought the sumproduct would work but is not returning a value,

View 11 Replies View Related

Sumproduct - Three Variables

Feb 19, 2008

I am trying to add an additional criteria to the following sumproduct formula. The formula below works fine to add up values that are within a date range. However, I want to add values within a specified date range as well as one additional variable. The additional variable is in column G.

SUMPRODUCT(--($A$3:$A$1015>=$A1026),--($A$3:$A$1015<=$B1026),D$3:D$1015)

View 10 Replies View Related

Sumproduct With Different Ranges

Nov 5, 2008

I'm attempting to do a sumproduct with different dimensioned ranges. I've read multiple posts that say that all ranges in a sumproduct must have the same dimensions. I've tried different commands (index/match/lookup, etc) with no luck. Perhaps someone can come up with a solution for me....

View 7 Replies View Related

Sumproduct Is Not Working

Mar 4, 2009

=((SUMPRODUCT((('Data Sheet'!$A$2:$A$997<2110000)+('Data Sheet'!$A$2:$A$997>2119999)),--(ISNUMBER(MATCH('Data Sheet'!$B$2:$B$997,{300100,302100},0))),('Data Sheet'!E$2:E$997*IF('Data Sheet'!E1<=$F$4:$G$4,1,0))

The last part of the formula is the problem, the first two arrays are conditions and the last array is a sum if f4 a period of the year is equal or less to the data sheet tab it is summing.The formula below works fine, but when the last condition is added it doesn't work.

=((SUMPRODUCT((('Data Sheet'!$A$2:$A$997<2110000)+('Data Sheet'!$A$2:$A$997>2119999)),--(ISNUMBER(MATCH('Data Sheet'!$B$2:$B$997,{300100,302100},0))),'Data Sheet'!E$2:E$997)

View 7 Replies View Related







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