I want to use Sumproduct function to sum up the values that belong only to Product "PXT" and "PCT". I enter it as array but my formulae doesnt work. can someone give me a hand. Here is my formulae: =SUMPRODUCT((C2:C10="PXT")*(C2:C10="PCT"))*(A2:A10)
ABCD
1ValueQtyProduct
2299.944PXT
3186.53PXT
4711.071PCA
5561.862PCT
6608.961PXT
7520.026PCT
8427.682PCA
9397.341PCA
10387.664PCT
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 RelatedThere is a date field and three "attempt" fields.
If The first attempt was a fail and the second a pass this must count as a pass and not a fail. If the first attempt was a fail and second was a fail then this must count as a fail, same again for third column.
On the totals tab there should be a total against each date for the fails. As you can see iv tried doing by sumproduct but its not working, i understand why its not working but i just can't see how I can get it right.
formula to take data from a column entry to a row style? I've attached a sample spreadsheet.
View 9 Replies View RelatedI have a function that has a formula sumproduct and here it is:
Set RSheet = Worksheets("Rawdata")
Set RSheet = Worksheets("Rawdata")
RSheet_lastRow = RSheet.Cells(Rows.Count, "A").End(xlUp).Row
RSheet.Range("AJ2:AJ" & RSheet_lastRow).Formulaarray = "=SUMPRODUCT
((V2>=ProjectedStarts!$K$1:$K$45)*(V2<=ProjectedStarts!$L$1:$L$45),ProjectedStarts!$M$1:$M$45)"
The problem is that when the formula is sent to each cell V2 (bolded) does not change to V3, V4, V5.
I have the the following formula =SUMPRODUCT(--(dealer1=$B36),--(make=$A36),--(company<>nominated))
The first two statements work alone, however I have an array which contains a list of customers that I wish to exclude from the total. I've put those in an array and called it nominated, However I just get #N/A.
I need to finish a final bit of tweaking in the following formula..
=SUMPRODUCT(--('[MRP Data File.xls]ZMM_CHECK_MRP_AREA'!$A$1:$A$10000=A9),--('[MRP Data File.xls]ZMM_CHECK_MRP_AREA'!$D$1:$D$10000="15BG"),--('[MRP Data File.xls]ZMM_CHECK_MRP_AREA'!$I$1:$I$10000""),'[MRP Data File.xls]ZMM_CHECK_MRP_AREA'!$R$1:$R$10000)
What I'm trying to achieve is to return the value of the cell in column R if column A=A9, D="15BG", this part is fine so far but I only want to return the value if column I = NOTBLANK
At the moment the value is returning whether it is blank or not...
For info: column I will only contain "CLC", "CLR" or totally blank
I am trying to filter an array (the longer one) using the shorter array as the criteria. I am currently doing this using the following method
IF(LOOKUP(lookup cell, array)=lookupcell, lookupcell, "FALSE")
I then copy and paste 'values' and filter out the 'false' to get my final result.
This has worked in the past, but for some reason that I simply can't figure out, the formula isnt working! I've attached the example, and I've highlighted a number in blue (cell E522 and C103), (that should be being found in the 'LOOKUP' function) but is returning a "FALSE". I have looked over the code and simply can't figure out why Excel isn't returning the right value.
This is obviously happening for a quite a few of my numbers, as my filter result is returning an array that is about 1500 shorter than it should be. I have highlighted E522 as the 'example cell' to look at.
Using Excel 2003, I'm summarizing info in one sheet (Review) with data in another worksheet (Work Orders). The following SUMPRODUCT calc worked fine...
View 2 Replies View RelatedI need to sum several non-contiguous columns if certain other columns meet a certain criterion.
Details:
Need to sum columns Q,R,S,T,U,V,W,X,Y,Z,AC,AD,AI,AJ,AK,AL,AM,AN,AP,AQ,AR,AS,AT,AU,AV,AX,AY,AZ,BA,BC,BD,BE,BF,BH,BI,BK,BL,BM,BN,BQ,BR
in sheet "(1) 65539004 SAP"
IF
column D in sheet "(1) 65539004 SAP" = A2 in sheet "(4) Brand Breakout" AND column L in "(1) 65539004 SAP" = $B$1 in "(4) Brand Breakout"
The formula will be placed in C2 in "(4) Brand Breakout" and copied to other cells just FYI,
I think I need an array formula or possible a sumproduct formula, to make this work
=SUMIF($BU$36:$BU$66,"X",T$36:T$66)
Column BU is a column that gets an X auto added to it each day, the date field runs down my spreadsheet from row 36 to 66. I use the X as month to date signifier so each day before todays date has an X in column BU. The formula above will then take whatever is in column T and adds up only the figures enter against the date before todays date.
This method works for most of my fields but column T is an average so I can't just add up the averages.
Column T is Average Handle Time, to work out the AHT for the month I presume I need some sort of array formula that also looks at the number of multiples the AHT by calls handled each day then divide the total minutes month to date by the total calls handled. How do I fit in an array formula that only works for the days when column BU has an X in it.
The calls handled field is column P
I've tried this but it doesn't work as a formula (the sumproduct part works outside of the sum if part).
=SUMIF($BU$36:$BU$66,"X",SUMPRODUCT($P$36:$P$42,$T$36:$T$42))
I've created a spreadsheet with SUMPRODUCT formulae, which is working fine for now.
However, these formulae include arrays with ranges of, for example, $G$2:$G$600. What we need to do is, instead, reference the while column as far down as it goes, forever as the range of the array. This applies to multiple occurrences.
Every formula I have found for this may work on of itself, but does not work with the SUMPRODUCT formulae I have used.
For reference, an example:
=SUMPRODUCT(--('BG NEW DB'!$D$2:$D$600=$B$2),--('BG NEW DB'!$G$2:$G$600=C$2),--('BG NEW DB'!$H$2:$H$600=$A$2),--('BG NEW DB'!$AD$2:$AD$600="Y"))
My problem is :
1.In G Column I put logic for Fail and Obtained Marks.
G2=IF(COUNTIF(B2:F2,">=60")=5,SUM(B2:F2),"Fail")
2. Now in H column I want use this formula which I obtained from this forum
H2=SUMPRODUCT((G$2:G$7>G2)/COUNTIF(G$2:G$7,G$2:G$7&""))+1
To get the position of Students.
But the text value "fail" in the G2:G7 getting Position No. 1 and i've noticed the reason by using evaluate formula as well.
3. I got solution by changing "Fail" with 0 by creating column I and then column H put 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 get students positions in a class like 1st, 2nd, 3rd, etc. There can be two 1st position and 2 2nd positions at a time .
1.In G Column I put logic for Fail and Obtained Marks.
G2=IF(COUNTIF(B2:F2,">=60")=5,SUM(B2:F2),"Fail")
2. Then i made array formula in column H to get the position
={SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1}
Some improvement to write fail & position
={IF(COUNTIF(B3:F3,">=60")5,"Fail",SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G3))/COUNTIF(G$2:G$7,G$2:G$7&""))+1)}
And more work to get total marks & position and Fail at the same time......
={IF(COUNTIF(B2:F2,">=60")5,"Fail",SUM(B2:F2)&"-"&SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1)}
Now I wounder that is there a way to do all calculations in column H without using column G
By replacing (if there is any method) G2, G3, G4 etc. with Sum(B2:F2); Sum(B3:F3);Sum(B4:F4) etc. in below formula
={SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1}.....................
I have a table with 3 columns with an unkown number of rows (text, date, date) that is being imported daily.
I want to create a 4th column with dates starting from today and each subsequent row be one day earlier. I want to look at 30 previous days.
I then want to count the number of rows (looking at column 1-3) with the following criteria:
Countif column2<= "date in colum4" AND column3< "date in column4"
What I’m trying to do is get a single cell to look at an array, if there is a number in that array which is between 2 limits to return that number. This is eventually going to work with limits of dates/times, and have to be updated once a day. I’ve attached a workbook with an example of the data and the way I want it formatted. {=IF(AND(A1:A25>=D32,A1:A25<E32),A1:A25,"")}
But this always returns a false. I can get it to work without using array and just having cell to cell logic but this means I would need the same amount of columns in the formatted data as the raw which is impractical.
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.
I need to check each of 13,000 job titles to see if they include the string "VP".
For the time being, I want to find the most efficient way to check an individual title, before building an array or SUMPRODUCT function that will check all the titles in swoop.
So... I have tried:
=ISNUMBER(SEARCH(A1,B1))
..but it returns TRUE if A1 and B1 are both blank -- which they can be in my spreadsheet.
And I have tried:
=SIGN(SEARCH(A1,B1))
but it returns #N/A if A1 cannot be found within B1.
If I can, I want to avoid introducing an IF statement because, looking ahead, I am already concerned that this analysis will be complicated enough without multiple paths.
So.. what's the most efficient way to return a TRUE/FALSE, or 1/0, result depending on whether A1 is in B1?
EXTRA-CREDIT!
An extension of the above problem... arrays and SUMPRODUCTs welcome:
What's the most efficient way to return a TRUE/FALSE, or 1/0, result depending on whether (A1 or A2 or A3... or A99) is in B1?
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'm trying to pull thesum of huge number of columns in this case the range of pulling the data is B2:E7 based on two criteria (B8 and B9) The result is on B11
Sheet1
*
A
B
C
[Code].....
All I am doing is counting text values in a table.
The table has 3 main columns(which are relevant to this thread anyway).
Shift - Area - Status
The example I have attached shows examples of DCOUNTA, SUMPRODUCT and a Pivot Table.
I have read many threads stating that the best one to use is Pivot Table followed by DCOUNTA followed by SUMPRODUCT.
The most effective for me seems to be SUMPRODUCT (although this does slow excel down dramatically when you use a lot of these formulas). As do Array Formulas
The Pivot Table does not update on its own, therefore constantly needs to be refreshed. (I could use code to do this)
The DCOUNTA seems to be the least effective at doing what I want (unless I am doing something wrong)
In the attached example can the DCOUNTA be used more efficiently as I don't like the fact that I am duplicating rows to apply the criteria for a different shift. e.g
Area 1 - Late Shift - Banned
Area 1 - Early Shift - Banned
I want my table to be as follows (as the SUMPRODUCT shows)
AREA - Early Shift - Late Shift - Night Shift - Area Total
Area 1
Area 2
Area 3
Area 4
Area 5
Shift Total
I should be able to do this by now but I keep getting an #NA error. I am
trying to use this sumproduct formula
=SUMPRODUCT(($I$7:$I$2585=921),--($L$7:$L$2585="Fb")--(AS7:AS2585)) where I
am looking up store #"921" and Attribute "fb and sum where these two criteria
meet. The column I want to sum is AS7:As285. What am I doing wrong.
In Column A1:A20, these are included 5 types of data (red, orange, yellow, green, blue); in column B1:B20, these are included the total salary amount, and in column C1:C:20, these are included "Sole" , "Joint" or "Partnership", so if column A is included red/yellow/green (excluding orange and blue) and column C is included Sole/joint/Partenship, and total salary is between 6000 and 9000, then calculate the total salary of all the customers. How can I use sumproduct function to fix it?
View 8 Replies View RelatedOk I'm writing an extraction formula. I've got my array of Trues and Falses, but now I need to go through each row and check if all the columns are TRUE, and then return the row if all the columns are true.
Right now I'm just working with a dummy set of data... my real table is some 50 columns wide and over 300 rows, but this gives you the idea of what I'm doing:
I tried putting in a Countif and using it like and array to count the different rows and return the number of times each row was returned... but countif goes nuts when you try an array for the criteria.
Formula:
=IF(IFERROR(($H$2:$J$13/B$1:D$1)>=1,TRUE),1,0)*IF(IFERROR(($H$2:$J$13/$B$2:$D$2)
I am looking for an excel formula that will allow me to perform the following logic: if a cell value in (M3:BA3)="D" then multiply C29 by the corresponding value in (M29:BA29), then sum all of the products.
View 2 Replies View RelatedHow can you test that no single element of an array is equal to a particular value?
I am trying to match 3 values, and return a matching 4th from a chart. It will first look at one value and see if it falls between values (the LOAD value in cell B1..it will look to see if it falls between the "low limit" and "high limit" in columns A10 to A135 and C10 to C135, respectively. Then it will look for a match for the values in cell A2, and in cell A3. the return value will index to the corresponding value in column G10 to G135. I have written a formula to do this in cell D2....but it will only ever return the value in cell G10.
See the attached excel file : nozzle tester.xlsx
I am looking around any way wherein I can sumproduct the values as given in attached sheet, basically I wanted to know the total MRP value of Sale and Stock
View 3 Replies View RelatedI'm working on a spreadsheet at work and I'm trying to sum only the values in a column that meet criteria in the column next to it. For example, in the attached spreadsheet, I would like to have a totals line at the bottom of the spreadsheet for all three Facilities and the total next to them. I know I could do a pivot table but I know this approach would be more aesthetic and easier for my supervisor.
View 3 Replies View Related