How can I get Sumproduct to work when the divisors are zero? So I get a #DIV/0! if anything in DU89:DU100 is a zero in the below
=SUMPRODUCT((DU23:DU34/DU56:DU67*DU89:DU100))
I had thought
=SUMPRODUCT((DU23:DU34/DU56:DU67*DU89:DU100)*(DU89:DU1000)) would work but that gives #n/a
I have a huge ss and my sumproduct function works only up tp certain columns and starts returning #value! error. here is the formula:
=SUMPRODUCT(--(Detail!$A$2:$A$2971>=$R$4),--(Detail!$A$2:$A$2971
=SUMPRODUCT(--($C$1:$C$17="S"),--($C$2:$C$18"S"))
how does this formula work?
It counts the number of instances in a list but not sure how
does the ranges have to be differant for this to work? ie C1:C17 C2:C18
my table is something like below:
my program sheet looks something like this:
I need a double condition formula to check the date, if matches the date Column (A), and matches product Column (b), then returns Column (C) which is the doc#.
I Could easily do it if it is a sum product formula, but in this case, it is alpha numberic.
date: 14 Jun (dd / mm)
product -- Doc#
apple ---- ac21 (so what formula do I put here?)
Orange --- abc2 (formula?)
date --- product --- Doc#
12 Jun -- apple ----- abc1
12 Jun -- Orange --- ab12
14 JUn -- Apple ----- ab21
14 Jun -- Orange ---- abc2
Now I'm trying to improve the functionality of the file attached above.I've attached the file again with another drop-down menu. What I'm trying to do is to have a sum of all the months between the two months indicated in the drop down menu.For example by picking two months from the drop down menus, I want to know the sum of the values between Jan-Mar (Jan, Feb and Mar), or for example Jul-Oct (Jul,Aug,Sep and Oct)...
I've tried to combine SUMPRODUCT with INDEX and MATCH but it doesn't work. I want to build upon this formula:
[Code]....
I forgot to mention that I have multiple strings (column E) which are NOT unique. Basically "VLOOKUP way" won't work, rather it should be "SUMIF way".
In the attached file I've added one additional row (r19), which is the same as r18, just to try how the summing will work.
Dynamic SUMIF(S).xlsx
I have a spreadsheet representing a month where I am trying to figure out different scenarios for employees. One scenario is that an employee could have to move to a temporary position. In that case, I need to calculate the salary payments to temporary employees in a particular work unit. I've tried several different approaches to this problem, but am still getting the error.
One method has been using this sumproduct formula:
i need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.
View 4 Replies View RelatedIn a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....
I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.
From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.
i have a made a macro that copies info to a new sheet now that is working great but if i change the name of the work book it wont work any more so i need the macro to work with what ever name i give the workbook
the current name is
AVERAGE PRICE (update 2009) Mimmos Armico 170809.xls
i have attached the code in notepad ...
In column J, Cad Hrs. How to make to display (0), instead of (-0,02) when I just do other work than Cad Work? I need to separate type of work according to price hour, overtime, and so on.
View 2 Replies View RelatedI have a range of cells in a work sheet "sheet 1 " my objective is to filter this range according to certain criteria (i ve succeeded to do this ) yet what i want to do now is copy this data to another existing worksheet in a certain range .
note :the existing worksheet to which i 'll copy the filtered data has some cells out of the range that i dont want to over write ..
Simply :how to copy a selected range of cells in a work sheet to already existing work sheet in a specific range aswell .
I am want to copy a formula across several work sheet and have the formula always take data from previous work sheet.
2) I am working with this formula =C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")
and it comes from this thread http://www.excelforum.com/excel-gene...orksheets.html. I have included a worksheet attachment that has explanations
Following code has 2 IF statements , the code runs the way it is and the first IF works perfectly, but the second IF does not work.
View 3 Replies View RelatedI 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 ?
I have the following formula:
View 4 Replies View RelatedI 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.....
How to add another sum to this formula?
this is the formula i have at the moment
=SUMPRODUCT(--($AA$21:$AA$6376
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?
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")))
=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.
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 RelatedI'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.............................
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 ...
=-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???
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
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 Relatedsum 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
screenshot.jpg
Ok, so from the attached picture (screenshot.jpg), you will see a data export of staff losses.
On the table to the right, i need cell G2 to take the ID from cell F2 and then search column A and add up every time there is negative number in column B.
So if staff ID has -15 , -15, -10 and -10 in the first table, their summary on the right table will show -50
(PS, i know it says AVERAGE LOSS) but i just need it to be the total. So basically, from the loss log on the left, it just counts the total losses occurred by each individual staff member on the summary table to the right.
Is there any way that Tab should automatically jump to next column, after the entry has been made.
I have only one number (one digit) per column. I wish that when I enter the digit the tab should go automatically to next column. And also if it is possible that tab may miss the blank column and go to the next where the entry is to be made.
I would like someone to explain the reason why when the program is run, the maximum value in column S successfully is selected by being colored in pink, but the minimum in column R between those pink max's (indicated by the entire row outlined in red) is not the minimum.
In other words, look at the numbers in column R. Only the 14.08 rows are supposed to be boxed in red. I can't understand why the 30.08 and the 46.08 are boxed in red??? And puzzling to me is why the 46.08 near the top are boxed, yet the 46.08 near the bottom are not boxed.