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 RepliesFord
Chevrolet
Cadillac Escalade
Yukon
Chrysler
Ford Taurus
Fusion by Ford
these strings are single cell values. Each has a corresponding column with counts of how many times these exact phrases were typed in. I need to do a sumproduct function (i think) that will sum all counts on car MANUFACTURER. Thus i have my own list that contains (Ford, Chevrolet, Cadillac, BMW, Audi, etc).
I want to count any instance for these, so in the example above, the phrases Ford," "Ford Taurus", and "Fusion by Ford" would all be included.
Attached is sample spreadsheet.
See attached file. this is excel 2003 and using sumproduct, but i cant get around with an if function.
View 3 Replies View Related=IF((-('Gained Flat Table'!AB2="FTL")),(SUMPRODUCT(-('Gained Flat Table'!$A$2:$A$2774="UK"),-('Gained Flat Table'!$E$2:$E$2774="ROAD LOGISTICS DOMESTIC FTL"),'Gained Flat Table'!$AA$2:$AA$2774)),0)
I tried to use only sumproduct function, the formula is working.
But when I add IF function, I get result as '0'. I couldn't figure out the problem.
I've got a sumproduct formula, and saw from another post how to add in an OR function.
Its working but what I need it to do is to exclude if it meets the OR function:
=SUMPRODUCT((rABReseller="Non-AB Reseller")*(EPP_Rev_Strm="New Lic")*(EPP_PA="PA")*(ISNUMBER(MATCH(N10:N54,{"Unassigned","To Be Assigned"})))*EM_PP)
So in the above, if it is "Unassigned" or "To Be Assigned", it should be excluded from the calculation.
I am trying to count number of staff working in any day without deleting the empty cells.(see attached sample sheet). I tried sumproduct as I have two criteria but it is giving me a "zero" result. I only want to count cells in range B3:B32 that does not have "#" sign if there is a staff name if there is a staff name id corresponding cell in Column A within A3:A32.
View 3 Replies View RelatedI'm having a hard time figuring out how to use the SumProduct worksheet function in VBA. The below worksheet formula works: =SUMPRODUCT((accProv=D2)*(accFYE=E2)*(accAcc))
- or -
=SUMPRODUCT(--(accProv=D2),--(accFYE=E2),(accAcc))
I tried the below variations in VBA, and I keep getting run-time error 13.
I have the following formula, which works; however I need to make it dynamic.
View 7 Replies View Related*ABC53TypeCost$Bought54Orange3Friday55Orange5Sunday56Apple4Friday57Orange5Friday58Orange4Sunday59Apple4Friday60Banana3Tuesday61Orange4Sunday Excel tables to the web >> Excel Jeanie HTML 4
Now, i want the Sum and Count of Orange which is bought on Friday
Answer Should Be
in D54 =8 and E54=2
=IF(LEN(A480),SUMPRODUCT((RIGHT(Sold!$D$15:$D$8998,LEN(A480))=A480)*(MONTH(Sold!$M$15:$M$8998)=12)),"")
I would like to edit the function above and add a third criteria. If Sold!S:S,"Returns" to my sumproduct.
I tried to edit it myself with the function below but it didn't work.
=IF(LEN(A480),SUMPRODUCT((RIGHT(Sold!$D$15:$D$8998,LEN(A480))=A480,Sold!S:S,"Returns")*(MONTH(Sold!$M$15:$M$8998)=12)),"")
convert this equation into sumproduct function..??
=COUNTIFS($I3,">="&$AC$3,$I3,"<="&$AC$4)
So I have a rather complicated IF, Countif, Sumproduct combo in a formula right now. As you can imagine this makes my worksheet run rather slowly. I was curious if there is any way to make this formula into a User Defined Function. The formula looks like this:
[CODE]
=IF(A15="","",IF((AND(COUNTIFS(J:J,J15,AM:AM,ABS(AM15),AD:AD,AD15)>1,
(SUMPRODUCT(--($J$17:$J$10000=$J15),--($AD$17:$AD$10000=$AD15),--(ABS($BD$17:$BD$10000)
=ABS($BD15)),--($AX$17:$AX$10000=$AX15),$BD$17:$BD$10000))
[CODE]
I would not be surprised if this isn't possible but thought I would try. Another possible approach would be to make this into a macro and instead of the output being "Duplicate" or "Research" just have the "Duplicate" Cells Highlighted in Red and the "Research" Celss highlighted in yellow.
Similar to SUMPRODUCT, do we have any function to count cells with diffrent critereas. Countifs does not work since the critereas are at different coulmn and rows.
View 1 Replies View RelatedQuery below
Date
Sales
1/7/2011
500
2/7/2011
600
[Code] ........
I was given the above table in an interview and was asked to find the sum between two dates without using sum and sumproduct.
I am trying to set up a keyword search where someone can type a word in D8 and the number of books with that word in its title will be returned.
I have managed to get the following sum to work if I put the exact title in the search however I would like it to be possible for a partial search.
=SUMPRODUCT(--('Books List'!G2:G1166=D8))
I am trying to do a basic count in a range, however am a little brain fried and its not working. On attachment, column D is to list all times out of column A and B listing only unique entries (have tried to use Macro with advanced filter however not liking it being an extract). Column E is to be a count of how many times the break time in Column D appears.
View 2 Replies View RelatedI have a worksheet that contains investment security purchases, including purchase date, purchase amount and maturity date, each in its own row. I need to develop a formula that will sum the purchase amount based upon the term in days (maturity date - purchase date) of the investment. The respective rows do not contain the term in days so the formula will need to calculate the days to determine the aggregating criteria described below.
One cell should contain the aggregate security purchase amount whose term is 1-90 days and another cell should contain the aggregate security purchase amount whose term is over 90 days. It seems that SUMPRODUCT with a condition is the best way to handle this; however, I cannot get the correct syntax of this formula.
I'm using a formula (with the SUMPRODUCT function) within which I need to match the month & year from an array with 3 "true" possibilities...
Meaning that if data (month & year) from the array matches any of the 3 months & year of a specific quarter (for example July 08, August 08, September 08), the result of that section should be "True".
The data to be matched (any of the 3 months of each quarter) is drawn from a table header (showing every month of the year) by using this "monthly" formula:
TEXT(H7;"mm.yyyy") where H7 holds "01.07.2008" (but displays "Jul. 08") and returns "07.2008".
I'm trying to extend it to something like:
TEXT({H$7;I$7;J$7};"mm.yyyy") to match either July 2008 (H7), August 2008 (I7) or September 2008 (J7) for the 3rd quarter of 2008 but when I hit Enter, I get an error and H$7 is highlighted ("ctrl + shift + enter" doesn't help).
Here's an excerpt of the formula that works well for other queries on my worksheet:
=SUMPRODUCT(--(TEXT('Current update analysis'!$O$2:OFFSET('Current update analysis'!$O$2;(COUNTA('Current update analysis'!$B:$B)+20);"mm.yyyy")=TEXT({H$7;I$7;J$7};"mm.yyyy")); --('Current update analysis'!$k$2:OFFSET('Current update analysis'!$k$2;(COUNTA('Current update analysis'!$B:$B)+20)="ok"))
how I could/should structure the formula for it to work?
I want to calculate a weighted average but I need it to only calculate on the months to date. I have a data validation drop down on a title page that is toggled to the current month each time a report is needed to define what months have actuals. I bring in data for all the months but only the current months have actuals. I need to calculate the weighted average on ONLY the ACTUALS. How can I set the ranges for sum product based on the data validation list on the title page?
View 4 Replies View RelatedI only want to include the Month of January in a sumproduct function on another page using a name manager array.
I was thinking of using
=SUMPRODUCT(INDEX(DATA,0,17),--((MONTH(INDEX(DATA,0,5)=1)*INDEX(DATA,0,17))
Where the dates are in the 6th column and include blank cells at the bottom and in the first row it says Date.
could provide me a loop function in VBA for these calcultions.
Cell A1*E1 + B1*F1 + C1*G1
Cell A2*E1 + B2*F1 + C2*G1
......till
CellA100*E1 + B100*F1 + C100 * G1
These are the calculations for E1-G1.
The process must be repeated till E50-G50
So the second step is:
Cell A1*E2 + B1*F2 + C1*G2
Cell A2*E2 + B2*F2 + C2*G2
......till
CellA100*E2 + B100*F2 + C100 * G2
I have a small problem using the ‘sumproduct function’ which I am using it to calculate the area under curves using the following equation (=SUMPRODUCT((B10:B109-B9:B108)*(D10:D109+D9:D108))*0.5). However I have hundreds of curves to calculate the area under and the length of the data series for each curve is different, I am currently changing the length of the data series by hand. I have attached an example worksheet of 2 sets of data (the formula I am using at the moment is highlighted in blue – row 203). Is there are function or formula that I can use inside this equation to change the range of the formula depending on the length of the data series?
View 2 Replies View RelatedI am using Office/Excel 2007 and Windows 8
I want to determine the number of values SUMIF/SUMPRODUCT functions used/checked to arrive at the answer, i.e. 95 for row 2 and 116 for row 3. A function that can give a 3 and 2 respectively.
Example:
Results for Row #2 gives: 95 with 3 values picked up under "Rate"
Results for Row #3 gives: 116 with 2 values though three places were checked up under "Rate"
How can I count the number of these values referencing at the "Rate" like in the functions used.
I managed to use SUMIF/SUMPRODUCT functions to calculate totals based on "Rate" in row one.
I tried COUNTIF function, but allows only field/heading.
A
B
C
D
E
F
G
[Code] ..........
results for each formula used
95 formula: =SUMIF(B$1:G$1,B$1,B2:G2)
95 formula: =SUMPRODUCT((B$1:G$1=B$1)*(B2:G2))
3 formula: =COUNTIF(B1:G2,B1)
I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:
[Code] ........
When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.
Another interesting dilemma to solve. Using this formula:
View 2 Replies View RelatedFunction Haversine has correct value in debugger but in cell it has the same value as Haversine2. Is this a known bug?
Public Function Haversine(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double
Dim temp As Double
[Code]....
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
View 9 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.....