I'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.
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 RelatedFord
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 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 had a workbook that I have been using to track inventory. I use a userform to enter the data for inventory in and out of the system. I recently added two more worksheets to the workbook, each of which has several SUMPRODUCT formulas on them that reference the inventory data.
Since adding these two sheets, when entering data via the userform, it takes a long time (relatively speaking) for the sheet to update with the relevant new data. I have attached a copy of the file, in hopes that someone can show me exactly what is causing the slow-down, and if there is a way that I can get the same results using different programming or formulas that will not be so slow.
The part that is slow is on the "Data" sheet. Click on the green or orange buttons to open the userform. Make a selection in each section, and enter an amount. Clicking on "Enter" will transfer the appropriate information to the worksheet. This is the part that is slow.
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 have a user defined function in one VBA module that I'd like to call from another module, so I would normaly scope it as "Public".
However, as I don't want it to be available to Excel as a worksheet function, I would also normally scope it as "Private"
Is there a way to have a function that is available to other VBA modules in the same project, but not to Excel as a worksheet function?
This line is returning the number of the last row with data in it in column 1 for the open worksheet:
View 3 Replies View RelatedMy workbook has a sheet called "MyList", another called "HisList" , a named range called "myNamedRange" which is "MyList!A:A".
How do I get this functionality of this excel formula
MATCH("b",{"a","b","c","b"},0) returns 2
how about if I would like to have the return result as 4 since I have another "b" within that same array?
I'v got a school task about making a excel worksheet.
But we have just started and i havent gotten all the functions to work yet.
The task itself is :
Make function that tests if D9 is larger then 100 and I9 is less then 50. If true "Kontroll" if not "OK" should be written.
Also, if "Kontroll" shows up it should have a dark green "OK " should just be black
Its roughly translated from norwegian. If you need some more information I will try to provide it
I've got the following array formula :
=MAX(IF(C:C="JAMES",ROW(C:C)))
Which nicely returns me the last occurrence of the name JAMES in column C
However, how would I translate this to VBA code?
I want to find the last occurrence of a value in a column and I prefer not to loop.