Making Complicated Sumproduct Into Function?
Jul 9, 2014
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.
View 1 Replies
ADVERTISEMENT
Nov 27, 2008
I am having some problems converting a working formula in excel into VBA code. The working formula is as follows;
=SUMPRODUCT(--(ReasonList=$B51),--(AgeList=0),--(CreatedList>=$D$47),--(CreatedList
View 9 Replies
View Related
Feb 14, 2014
I am trying to automise a workbook entry that Allocates Unique Invoice numbers depending on the job type and so that each job is only allocated one Invoice Number. Column A contains the Unique identifier, the customer name; Column C contains a drop down box with the 3 options for invoice type; "Monthly", "One off" and "Job"; Column D contains the Invoice number.
Assuming data has been entered up to row 10 I am trying to write an IF formula in column D, with the 3 conditions:
IF C11="Monthly" then D11= Vlookup for Unique Identifier, the customer name in A11; table array is A1:A11; Col_index_num is an earlier Invoice Number in column D that belongs to the same customer name, and D11 is left blank
2nd condition
IF C11="Job" then D11= Vlookup the Unique Identifier, the customer name in A11; table array is A1:A11; Col_index_num is an earlier Invoice number in column D that belongs to the same customer Name, and D11 is left blank
3rd condition
IF C11="One off" then D11= one value above the maximum (D1:D10)
Example:
Name (unique ID)DateInvoice TypeInvoice Number (IN)Explanation
White 1/2/14One off100 Initial (IN)
Brown 2/2/14Monthly101 Highest IN + 1
Smith 3/2/14Job 102 Highest IN + 1
Smith 4/2/14Job Blank, as it is the same Job as on 3/2/14
Brown 5/2/14Monthly Blank, as only gets invoiced once a month, with one IN
Peters 6/2/14One off103 Highest IN +1
Brown 8/2/14Monthly Blank, as only gets invoiced once a month, with one IN
View 1 Replies
View Related
Apr 24, 2008
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
View 10 Replies
View Related
Nov 13, 2008
i am trying to get the following function to work
i want it to look at 2 cells, depending on the number in the second cell i want it to multiply the first sell by a specific number.
for exaple (Below) function in a3 looks at a2 sees it is a 2 and multiplies a1 by 1.09 shown in the code below
A1=load
A2=factor
A1 A2 A3
100 2 Function
Function Pmax(Load, Factor) As Double
Select Case (Factor)
Case "1"
Pmax = Load
Case "2"
Pmax = Load * 1.09
Case "3"
Pmax = Load * 1.12
Case "4"
View 9 Replies
View Related
Dec 10, 2013
Basically i have a list of data for example from B2 to B2000 for which i want to calc a standard deviation across however the number of data points in the standard deviation need to be dynamic.
For example in Cell a1 i can enter 150 it will then calc the standard div across the first 150 data points (points 1 to 150) then in the cell below calculate the next standard div.
Standard Div Points10
Data PointData ListStandard div
10.167442459
20.539073451
30.29740845
40.309440859
50.640742715
[code].....
View 2 Replies
View Related
Apr 16, 2002
I've got a couple of user-defined functions that I coded in via one workbook (wkb A), but would like these functions to be accessible to any workbook (wkb B...Z). How is this done so that when I start a new blank wkb I can use these user-defined functions there as well?
View 9 Replies
View Related
Feb 15, 2010
i have some numbers as data, i want to find the function that generate thiese numbers and also i want to view the chart of it, its kind of sine wave graph.
View 9 Replies
View Related
Nov 23, 2007
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 Related
Oct 1, 2009
Ford
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.
View 2 Replies
View Related
Jan 18, 2009
See attached file. this is excel 2003 and using sumproduct, but i cant get around with an if function.
View 3 Replies
View Related
Dec 23, 2008
=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.
View 9 Replies
View Related
Feb 11, 2010
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.
View 9 Replies
View Related
Mar 9, 2009
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 Related
Feb 5, 2009
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.
View 3 Replies
View Related
Dec 29, 2009
I have the following formula, which works; however I need to make it dynamic.
View 7 Replies
View Related
Jun 9, 2009
*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
View 9 Replies
View Related
Feb 5, 2014
=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)),"")
View 3 Replies
View Related
Feb 25, 2014
convert this equation into sumproduct function..??
=COUNTIFS($I3,">="&$AC$3,$I3,"<="&$AC$4)
View 6 Replies
View Related
Dec 12, 2011
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 Related
Jun 23, 2012
Query 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.
View 3 Replies
View Related
Oct 30, 2009
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))
View 9 Replies
View Related
May 6, 2009
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 Related
Jun 11, 2009
I 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.
View 8 Replies
View Related
Aug 25, 2008
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?
View 10 Replies
View Related
May 31, 2006
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 Related
Dec 27, 2013
I 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.
View 14 Replies
View Related
Dec 19, 2007
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
View 12 Replies
View Related
Feb 20, 2007
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 Related
Aug 2, 2014
I 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)
View 9 Replies
View Related
