(SUMPRODUCT(--('Job'!$F$2:$F$1000="CCTV"),--('List'!$S$2:$S$1000="pass")))
There is another column with 'released' date (date the job was raised)
How would i implement a formula to calculate the average age of a certain job type in the above example?
Can i use SUMPRODUCT and average together? ?
I am using sumproduct to sum the total number of transactions in each country. The argument is =SUMPRODUCT(--(Sheet1!$C$1:$C$9999),--(Sheet1!$AB$1:$AB$9999=Sheet2!A4))
The Firts part C:C is a list of every transaction value, the second is the country to which the transaction took place in. is there an easy way to obtain the average transaction value as te countries are spread over the list?
I have 3 excel sheets (A,B and C) linked to a master sheet (X). In each sheet (A,B and C), people enter unique idetifier in first column, its type (mr, ab, J) in the second column, start date in the third column and finish date in the fourth column. At the end of every week, i subtract each start date from the finish date and get the number of days spent for each unique idetifier (column N). I preiously posted a thread (Calculate difference between dates excluding weekends) to inquire about how to calculate the difference between finish date and start date and exclude the weekends. I received some nice solutions, but none has worked as the start date cane be weekend as well.
My second and major query is once i have the difference in weekedays for each unique identifier in all the three sheets, I want to calcuate the average of each identifier type (mr, ab and J) in the master sheet. In the master sheet, i have used sumproduct to calculate the average of each idetifier type from column N for each sheet. That means i have a column for A sheet, subdivided into three types, which include the average. I am using sumproduct for this. Once this is done for each sheet type, i need a final colum in the master sheet that calculates average of all mr from A, B and C together, and similarly for ab and J. The problem here is if i combine all the sumproduct formula, i am not gettng the right answer.
My formula looks like SUMPRODUCT((SheetA!$E$5:$E$40="mr")*(SheetA!$M$5:$M$40)) for all mr in sheet A. I then divide this by SUMPRODUCT((SheetA!$E$5:$E$40="mr")*(SheetA!$M$5:$M$40<>"")) to get the average of all mrs in sheet A. I do the same for sheet B and sheet C for all three identifier types. Now i want to combine the formula for all sheets together under mr, Ab and J. I thought the following should work ((SUMPRODUCT((SheetA!$E$5:$E$40="mr")*(SheetA!$M$5:$M$40))+(SUMPRODUCT((SheetB!$E$5:$E$40="mr")*(She etB!$M$5:$M$40))+(SUMPRODUCT((SheetC!$E$5:$E$40="mr")*(SheetC!$M$5:$M$40)))/((SUMPRODUCT((SheetA!$E$5:$E$40="mr")*(SheetA!$M$5:$M$40<>""))+((SUMPRODUCT((SheetB!$E$5:$E$40="mr") *(SheetB!$M$5:$M$40<>""))+((SUMPRODUCT((SheetC!$E$5:$E$40="mr")*(SheetC!$M$5:$M$40<>""))).....
But this is giving me wrong value. It is working fine when done for individual sheets, but when combined for all three sheets together, the value is wrong.
I’m using the following formula:
SUMPRODUCT(--(Issue="Yes"),--(Month=Aug),--(Building=$A$9))
To find the number of instances where there was an issue in a certain building during a certain month.
What I can’t figure out is how to find the Average number of such instances for the past 3 months, the last 6 months & the trailing twelve months. If I change the formula to:
SUMPRODUCT(--(Issue="Yes"),--(Month>April),--(Building=$A$9))
I get the correct sum for the months of May, June, July & Aug but I need an average number of instances per month for the last three, last 6 and TTM but not to include the current month.
I need to average with multiple conditions. Is there a way for sumproduct to do such a thing? How to average with conditions?
View 3 Replies View RelatedI've been trying to figure out a dollar weighted average formula in excel.
See attached file...
In sheet 1, I need to pull data from sheet 2 using a sumproduct formula to find the dollar weighted average maturity of a bond portfolio. Basically, I need to know how many days the portfolio has left to mature according to the portfolio's weighting by the amount in column H.
I am pulling data from a database and want to do an average based on data that was input per day, basically I did a SUMPRODUCT in a different file to avoid having the source open, but I can't make it average the data and disregard if there were ZEROS in that day.
e.g.
Column A Column B Column C
6/25/2013 A 1.3
6/25/2013 B 1.45
6/25/2013 C 1.9
6/25/2013 D 0
In the other file I would need to summarize the average of the Data from COLUMN C based on the day but without counting the zeros
I have data table with sales numbers by product, its wheel base, and over platform for months ranging from Aug 2013 - Nov 2013. This data table will populate with the complete month sales numbers after the month has finished going forward.
I have a table to the right of the listing the sum totals for each product (by wheel base and platform) by each quarter (rolling).
Now I need building a sumproduct average to calculate the quarterly average. This is not that simple because not all products (platform/wheel base) were available the entire quarter.
Also PLATFORM 3 in the tables are grouped - instead of listing platform 3a and platform 3b - using this in the formula:
(LEFT($D$2:$D$100,LEN($J9))=$J9)
Because not all products were available for the entire quarter - and the formula will need to count how many months that product, platform, wheel base was available during that quarter I'm guessing "=MOD(MONTH(A1)-1,3)+1" this may have to be used to count the number of months into the quarter the product was available.
Attached file. [URL] ........
I trying to convert the following formula (I, II, III) and add criteria of Name and Date to the count values of “c”
CURRENT FORMULA
I: “=COUNTIF(C2:C3100,"
I am trying to calculate some averages. What I have is 3 columns of data in A, B, C, also the "tasks" in A are in named ranges ex: "Award Contract" is a named range - "Task_Award" and "Confirm Updates" is a named range - "Task_Updates". I've attached a sample excel sheet.
I'd like to be able to create a macro to evaluate column A, and for every row in range "Task_Award", give me the average of the corresponding cells in column C and put it in the same range of cells in column B , then, for every row in "Task_Confirm" then give me the average of the same range of cells in column C and place the result in the same range of cells in column B. This is my very first post so I hope I am doing this correctly. I have 77 of these task ranges to evaluate and it will take a long time to do it manually. I'm thinking of a loop function.
Trying to sum values in column A, if values in column B have a range between 0-9.
or can we use less than < 9.
using Sumproduct formula
Looking to troubleshoot using a name range in a multiple criteria sumproduct formula.
For instance, this formulas works fine:
[Code]......
But, when I try to substitute the two-alpha codes as a defined name range ("keys"), it errors as "N/A":
[Code].....
An example workbook is attached : Example Workbook.xlsx
I am trying to sum figures within a specific date range in the following formulae.
=SUMPRODUCT(--(Bookings!G$7:G$576=B17),--(AND(Bookings!A$7:A$576>=$C$12,Bookings!A$7:A$576<$C$12+7)),Bookings!J$7:J$576)
Currently I get an error and I am certain it is in this part;
(AND(Bookings!A$7:A$576>=$C$12,Bookings!A$7:A$576<$C$12+7))
This is trying to look for dates that are greater than or equal to the one in $C$12 AND less than 7 days later.
I am keepting track of all the calls i get so the range changes daily. In A
is the date and column J is who took the call.
Is there any way to take this formula and have it refer to one place for the
range end as my table grows?
=SUMPRODUCT((MONTH(Data!$A$5:$A$670)=MONTH($A25))*(YEAR(Data!$A$5:$A$670)
=YEAR($A25))*(Data!$J$5:$J$670=C$18))
I have below four column range. I need a formula to sumproduct column A and column D, where column B = "n1", column C = "xyz" and until sum of column A reaches first largest value which is less or equal to a variable, say 15. So, the rows would be 1st, 2nd and 5th. And the result - 1,440.
A B C D
2n1xyz110
5n1xyz112
8n2abc112
3n1abc111
6n1xyz110
6n1abc114
3n1xyz114
2n1abc112
3n2xyz114
8n1xyz114
8n1xyz115
4n1abc115
I have worked our an CSE formula below, but it is really massive. Need to have much simplier one.
={SUMPRODUCT(--($C$1:INDEX($C$1:$C$12,MATCH(LARGE(IF(($C$1:$C$12="xyz")*($B$1:$B$12="n1"),$A$1:$A$12),COUNTIFS($C$1:$C$12,"xyz",$B$1:$B$12,"=n1")-SUM(IF(FREQUENCY(IF(MMULT(--(ROW($A$1:$A$12)>=TRANSPOSE(ROW($A$1:$A$12))),--IF(($C$1:$C$12="xyz")*
[Code]....
A B C
Date Product qtys
October 4, 2006ADSD403 9
October 5, 2006ADSD403 9
October 15, 2006ADSD403 4
How many skids (Located in column c) of product code (located in Column B) are received between October 4-October 13.
I have a sheet with data in A1:A50 also in B1:B50 and amounts in C1:C50
I calculate using the formula sumproduct((a1:a50="yes")*(B1:B50="RED")*(C1:C50)). How can I change this to allow for rows being added on a regular basis.
I am performing a sumproduct calculation in an array form. The first three columns in the data table have criteria, " Name", "Letter" and "Multiplier". The following 5 columns have days of the week, "MON", "TUE" etc. What I am trying to achieve is to use a sumproduct array to muliply the "Multiplier" criteria against a particular "Day" criteria when "Name", "Letter", and "Day" criteria match a series of reference cells. What this requires me to do is to define the "Day" range of values differently when I change the "Day" criteria. How do I create a variable range?. example file attached.
View 4 Replies View RelatedI'm getting adding values from a worksheet using sumproduct. The formula is as follows:
=SUMAPRODUCTO((Datos!$F$2:F459)*(Datos!$A$2:A459=Tendencias!E3)*(Datos!$C$2:C459=Tendencias!$A$5))
But when i replace the range from row 459 to row 55000 i get the NA.... i need the range to be as big as possible as i keep adding data on a daily basis.
what do i need to add to the formula?
Im Trying to use a formula of countif or sumproduct
so the formula will read the range of cells from A2:A30 to see if the word Hydro is in any of those cells, then if the date range from Cell B2:B30 is from range =>Dec-01-2007 to =<Dec-31-2007, then it will count 1.
[Code] ........
But this formula doest count.
I've tried using a multiple if count if with arrays and sumproduct. I really want it to count the ammount of time the work hydro is used during the month on december .
What is the formula for sumproduct to count a say column c for a range of names and as long it match column k for yes.
View 1 Replies View RelatedI have a "RANKIF" formula using SUMPRODUCT:
=SUMPRODUCT(--($B4=$B$4:$B$100),--($F4
I have been having problems with a function trying to count data within 2 date ranges.
When inserting the 2 dates required into collum B2 (week beginning) + B3 (week ending) it does not
I have a series of incident reports (some of which are injuries), and I'm calculating the number of incidents per body part per month using:
=SUMPRODUCT((ISNUMBER(FIND($D4,owssvr!$Q$2:$Q$2188)))*(MONTH(owssvr!$A$2:$A$2188)
=MONTH(math!F$2))*(YEAR(owssvr!$A$2:$A$ 2188)=YEAR(math!F$2)))
In which D4 is the body part (Neck, for example), owssvr is the sheet with the records being summarized and F2 contains the month being queried.
The problem I'm having is that I want to calculate data for a region of the body (head & neck), which will include count any record that has a part of that body region (nose, face, eye, tooth) mentioned in the affected area text. If I simply sum all the values calculated for each body part, records that include multiple parts (e.g. "scratched nose and eye") will be counted twice.
Can I calculate whether a range of cells for the incidents contains any of a specified range of body parts (listed in D4:D15), but do this for each month, and count each record only once (e.g. "cut nose" = 1 record, "cut nose & eye" = 1 record).
Is 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 have this:
=INDEX(SUMPRODUCT((D:D>=DATE(2013,6,6))*(D:D
Why won't this SUMPRODUCT work?
SUMPRODUCT(Sheet2!D1:D2000=OPEN,Sheet2!F1:F2000)
I have a range (D1:D2000) that contains a 'Status' value.
OPEN is a named range that refers to eight possible Status values: Assigned, New, Hold, Re-test, In Progress, Failed, Ready for Push, Coded
The formula should sum the values in F1:F2000 for any row containing one of the eight statuses in D1:D2000.
I'm still using XL2003 (Heck, they JUST upgraded from 2002 last year! So at this rate, I'll probably get 2007 here in the office by 2012).
I have a few named ranges that use Indirect & Count functions to auto-adjust the range to the last row. Something like this.
Name: ProjectID
Range: Sheet1!$A:INDIRECT("$A$"&COUNT($A:$A))
When I try to include the named range in a sumproduct statement, it crashes XL the moment I type the evaluative sign (< = >) within the 2nd bracket. Like this:
=SUMPRODUCT(--(ProjectID>2500))
(The actual formula works on several other columns of data. I'm citing a simple example, which also crashes my XL.)
Is that my installation causing the error or something wacko in XL? Please try and write back.
I want to calculate
=SUM(A1:A5)*A6+SUM(B1:B5)*B6+SUM(C1:C5)*C6...
Can I do this with SUMPRODUCT - without listing all the summed ranges out separately?
i have a large set of data and the sumproduct formula i have is extremeley slow ( half the time excel crashes)..plus i will need to be updating this most days!
this is the formula i have in each cell -
=SUMPRODUCT(--($L:$L=Y$3),--($D:$D=$N6),--($F:$F=$O6),($C:$C))
i have tried a sumifs version but it keeps coming up with zero.
would anyone have any help as to how i could get this for formula changed so that it isnt crashing my machine over a large range?
