How do i change the formula, =SUMPRODUCT(c14:au14,c15:au15) to include more columns added to row 14 and 15 in future.
Since the column count will increase , the sumproduct has to be like
=SUMPRODUCT(c14:"last cell in 14th row", c15:"last cell in 15th row")
I have this formula -
=SUMPRODUCT(('[Australia Load Tracker - March 2014 - V2.xlsm]QA'!$E:$E=B4)*('[Australia Load Tracker - March 2014 - V2.xlsm]QA'!$D:$D=$A$2))
Which works perfectly if A2 has the cell value 24/03/2014 however, if i put in $A$2 this value
="02"&"/"&$XFC$1&"/"&"14", where XFC$1 equals =IF(XFD1>9,XFD1,"0"&XFD1) and XFD1 = =MONTH(TODAY())
Which in basic term gives the month value "03"
The cell shows the value 24/03/2014 however does not work with the sumproduct.. is there anyway to get sumproduct to read this cell value as 24/03/2014 as the whole point is so every month I don't have to update anything and it will constantly update...
The only alternative i can think of is quickly making a VBA script to hardcode the cell values for the dates and change the month value for the middle of the cells every month.
I have formula but would like to change the ="03-01-2011" to reference what is in a cell =a2, etc.
=SUMPRODUCT(--(LEFT($B$2:$B$3582,10)="03-01-2011"))
I am trying to use the sumproduct function with ranges defined in my VBA code. I can’t seem to get it to work.
I have two spreadsheets in the enclosed work book – T1, T2
T2 has four columns with the first three columns are text and the fourth column is an integer. The rows in this sheet may be repeated.
T1 has four columns similar to the first three columns in T2. However, the rows are unique. I am trying to do a sumproduct for obtaining the counts on the fourth column in sheet T1
To obtain the values for the fourth column (for each row) in T1 I do the following ...
I am trying to use SUMPRODUCT on one of my excel files. the condition is that it will try to count all those cells that contain certain letters. i tried using this formula, =SUMPRODUCT(--('All Computers'!$A$2:$A$100000="USA*"),--('All Computers'!$E$2:$E$100000="June")), but I am getting a 0 value.
View 3 Replies View RelatedSUMIF allows one to enter a [valid] criteria text string into cell D2, say
">14000", and then reference D2 as the function criteria, e.g.,
=SUMIF(B5:B10,D2,C5:C10).
Is there a syntax that SUMPRODUCT will accept that does the same thing? Note the inclusion of a comparison operator is important.
I have the following line of code in the code for a textbox in a userform:
View 4 Replies View RelatedWith the formula I have as:
=SUMPRODUCT(--(GLCodes=$B7),MasterNetAmt_01)
I want to dis-include the CC="06" in the summation. I tried using:
=SUMPRODUCT(--(GLCodes=$B7),(cc="06"),MasterNetAmt_01)
But it gives a wrong calculation
I use "--" because i have character in the cell
{=SUM(IF(MONTH('3107'!$A$2:$A$188)=MONTH($A6),'3107'!F$2:F$188))+SUM(IF(MONTH('3207'!$A$2:$A$188)=MONTH($A6),'3207'!F$2: F$188))+SUM(IF(MONTH('4107'!$A$2:$A$188)=MONTH($A6),'4107'!F$2:F$188))+SUM(IF(MONTH('4207'!$A$2:$A$188)=MONTH($A6),'4207 '!F$2:F$188))+SUM(IF(MONTH('4307'!$A$2:$A$188)=MONTH($A6),'4307'!F$2:F$188))+SUM(IF(MONTH('AS21'!$A$2:$A$188)=MONTH($A6) ,'AS21'!F$2:F$188))}
Failed to convert using
=SUMPRODUCT(--((IF(MONTH('3107'!$A$2:$A$188)=MONTH($A6),'3107'!F$2:F$188))+SUMPRODUCT(--(IF(MONTH('3207'!$A$2:$A$188)=MONTH($A6),'3207'!F$2:F$188))+SUMPRODUCT(--IF(MONTH('4107'!$A$2:$A$188)=MONTH($A6),'4107'!F$2:F$188))+SUMPRODUCT(--(IF(MONTH('4207'!$A$2:$A$188)=MONTH($A6),'4207'!F$2:F$188))+SUMPRODUCT(--((IF(MONTH('4307'!$A$2:$A$188)=MONTH($A6),'4307'!F$2:F$188))+SUMPRODUCT(--(IF(MONTH('AS21'!$A$2:$A$188)=MONTH($A6),'AS21'!F$2:F$188)))))))))
A6 is a month column and looks like this "Jan-08"
the '3107', '3207', '4107', etc. are the names of specific sheets, so I'm summing over multiple sheets.
I have a spreadsheet with 3 coloumns. In A I enter Address, B Date, C Ward/District Area (this is a data validation list). At present I use; = SUMPRODUCT((HFRA!B4:B2000>=Summary!B1)*(HFRA!B4:B2000<=Summary!B2)). Summary B1 and B2 are dates to search between e.g 1/01/07 and 31/01/07. The above formula only counts the date enteries between B1 and B2.
I would like to change this so that it still checks that the date still falls between B1 and B2 but is adds together the numbers entered in coloum A. So coloumn A would become number of completed actions not an address.
In reference to an earlier thread, which shg and ByTheCringe2 both were genereous in their assistance, I would like to add a wrinkle, as requested by the end-users.
how to reference an earlier thread for people to reference but you can search " Sumproduct By Last (criteria) Month With Data". Edit by ByTheCringe2: Sumproduct By Last (criteria) Month With Data.
We had used the MAX function to find the most recent investments, per counselor, per bank. I now need to find the most recent investments as of a certain date (month-end perhaps). I have ideas, but I'm afraid my misperceptions/assumptions might hinder me from seeing all available options. Initially, I thought I could use a text or listbox on the summary sheet to enter a date that would link to the reference cell on the data page, and this reference cell would then be used in whichever formula I came up with.
I have the following formula in a cell that gives me a sum of items matching certain criteria.
View 3 Replies View RelatedI am trying to get the sumproduct of a column of amounts based on the adjacent cells ID code.
For example column B will have codes 1FHD, 2FHS, 3FHG etc. these codes will be mixed otherwise I would have just did a sum of the cells.
Column C would have dollar amounts.
Sumproduct based on adjacent cell ID Code
To calculate the result for a month is easy, but I can't figure how to get a single cell formula to calculate for the year. The sample attached explains it better.
View 4 Replies View RelatedI am currently using this formula -
=SUMPRODUCT(--(MONTH(Leads!B3:B999)=1),--(YEAR(Leads!B3:B999)=2013),--((Leads!J3:J999)="Contract"))
This gives me the count of rows that have Jan as the month, 2013 as the year and Contract word in column J. Now I want to add another column to be checked. That column has values in format - Design: X, Design: Y, Design: Z etc.
So I want to have the condition above and the cells that have the word Design.
I have 2 problems:
1) I am doing multiple sumproducts on 6 sheets within a workbook with last row now being in the 12,000 cell range. Since putting in this SumProduct, my excel is extremley slow and affecting other windows applications. Is there anything I can use to replace this sum product ?
=SUMPRODUCT(--(TEXT($B$4:$B$9003,"mmddyyhh")=TEXT(P2273,"mmddyyhh")),$D$4:$D$9003)
It seems to be starting the calculations even before I put any values in column D ? I have already tried the manual calculation, but it is not making much of a difference.
2) After doing the sumproduct, I then need another row to look at the value of the sumproduct cell and report it in another sheet. My problem is that the cell contains the date and time in 1 cell, but in order to get the cell to lookup the value properly I need to delete the 00 in the seconds to refresh the cell or else it gives me back an n/a# value, even though the seconds are 00 and not 01 or 02 etc ?
example : ....
My current formula is this: =SUMPRODUCT((Str311A=1)*(Str311C=15)*(Str311G<>" DM "),Str311L). All the items starting with Str311 are formula's associate with a name. What I'm trying to do is add one more factor into the formula. I need it to look in column I for any cell that contains the words L/S. I tried modifying the formula to this but it does not work:
SUMPRODUCT((Str311A=1)*(Str311C=15)*(Str311I="*L/S*")*(Str311G<>" DM "),Str311L)
The formula contained inside the names are:
Str311=OFFSET(Outlet!$A$1,MATCH("STORE # 163311",Outlet!$A:$A,0)-1,0,MATCH("TOTAL FOR STORE : 163311 SAN MARCOS",Outlet!$A:$A,0)-MATCH("STORE # 163311",Outlet!$A:$A,0)+1,1)
Str311A=OFFSET(Str311,0,0)
Str311C=OFFSET(Str311,0,2)
Str311I=OFFSET(Str311,0,8)
Str311G=OFFSET(Str311,0,6)
What I need is a way to modify my original formula to now also only match cells that contain the phrase L/S. The phrase can be located at the beggining, middle, or end of the cell characters.
I've got the following sumproduct formula (which seems to work):
=SUMPRODUCT(--(ISNUMBER(SEARCH($B17,Product_Keywords))),--(YEAR(Invoice_Date)=H$3),--(ROUNDUP(MONTH(Invoice_Date)/3,0)=H$4),--(Invoiced_Amount))
B17 is a keyword to be found in the Product_Keywords range
I'd like to modify it so that:
ifthe cell in the range Product_Keywords also includes "," thendivide the sumproduct by the number of commas+1
Presumably I could add --(ISNUMBER(SEARCH(",",Product_Keywords) as one of the conditions,
but how would I keep track of the number of commas in the cell?
I am very comfortable with SUMIF, but need to search a range any any cell containing words or phrase "20%" then need to sum those corresponding cells only.
I.e., something like: SUMIF(A1:A10,..."20%",C1:C10) .... only sum all C cells where an A cell contains any combination of words with "20%" in them.
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 ?
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???