Expand On Existing Sumproduct Formula
Feb 26, 2014
Still finding the SUMPRODUCT function a bit challenging to fully grasp.
This formula works great to sum the miles (Q16:Q38) the team (A16:A38) drove in a particular month (B16:B38)
=SUMPRODUCT(--ISNUMBER(MATCH(A16:A38,INDIRECT(E3),0)),--(MONTH(B16:B38)=E7),Q16:Q38)
How would I expand it to check yet another column say (S16:S38) for the term " Charge Call" ?
So I can see the miles the team drove in March for all the charge calls.
Where can I go to study and learn more about SUMPRODUCT. Seems like just what I need to build interactive type dashboard reports.
View 2 Replies
ADVERTISEMENT
Mar 31, 2009
I have a cell set up with a formula to copy the content from another cell. However, the cell won't expand to fit the text. If you delete the formula and just type in the cell, it does expand. See attachment.
View 5 Replies
View Related
Dec 8, 2006
Need to expand the crteria on this array?
{=SUM(IF(LYIND1=1,IF(PROFILE!$G$405:$G$435="EOM",(PROFILE!$AU$405:$AU$435),SUM(IF(LYIND1=2,IF(PROFILE!$G$406:$G$436="EOM",(PROFILE!$AU$406:$AU$436)))))))-SUM($K$16:N16)}
I need to add
{=SUM(IF(LYIND2=1,IF(PROFILE!$G$406:$G$436="EOM",(PROFILE!$AU$406:$AU$436),SUM(IF(LYIND2=2,IF(PROFILE!$G$407:$G$437="EOM",(PROFILE!$AU$407:$AU$437)))))))-SUM($K$16:N16)}
View 3 Replies
View Related
Jan 25, 2014
The attached workbook has a fragment of a table . The column heads are letters representing job nos. The first row is of numbers which will always be multiples of 1000. Subsequent rows are the factors of each number by each number.
My problem is how to structure the columns of the second table so that if a number has a factor of 1 there is one copy of that column head. if the number has a factor of 2 then there are 2 copies of the head, and so on.
The table can vary in size but the first row of each column will always be an integral number of 1000's (e.g. never 1250).
I have not been able to attach the workbook as "ADDFILES" is not visible in the download screen, I hope the above description will be enough.
View 3 Replies
View Related
May 17, 2007
I believe it should be quite a simple thing...and probably has something to do with the OFFSET function...but I cannot seem to put my fingers to it.
All details are mentioned in the attached ZIP file...so I won't repeat myself here.
View 10 Replies
View Related
Mar 27, 2014
I have an existing formula in a cell linking back to a start date only. determining how to add an additional formula to exclude weekends, and possibly holidays.
View 2 Replies
View Related
Aug 12, 2007
This task joins a string together based on a number of characters per cell in the range.
I want to isolate one range, Col N, and add an IF condition to it.
There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....
View 9 Replies
View Related
Jul 6, 2014
=IF(E14<=0,0,IF(N9="yes",MAX(E15*C15,30),30))
I am currently using the above formula and need to make an addition to it.
If D8 is greater than 9000 and less than 9999 then the entry will be 35 rather than 30. Any other entry in D8 would leave it at 30
View 5 Replies
View Related
Apr 3, 2014
In my attached spreadsheet example below, in the Rollup Data sheet, I have a formula (Cell: C17) that will give me the actual cost totals that has been invoiced on WBS Element (DWRRI-BW096-231) for a total of $35,004.81.
What I need the formula do is run a redundant check procedure on my new WBS Element (DWRRI-BW096-231) actual cost total formula in the Rollup Data sheet (Cell: C17) against/vs. what the SAP system provides in the raw SAP Excel export contained in the PTD-Actual Cost sheet for the WBS Element (DWRRI-BW096-231) actual cost total of $35,004.81 (Cell: F9) that's highlighted in the yellow row.
In the Rollup Data sheet (Cell: C18), I need this formula to perform a similar function the as the previous formula above it (Cell: C17), but it this formulas function (Cell: C18) would have a separate operation that would only look for and return the single/sole WBS Element (DWRRI-BW096-231) actual cost total contained in the PTD-Actual Cost sheet (Cell: F9).
In the Rollup Data sheet I could then compare my two WBS Element (DWRRI-BW096-231) actual costs totals, the first from the automated addition of all the individual matching WBS Element actual costs into an actual cost total (Cell: C17), the second (new formula I'm requesting) from the matching and return function of the single/sole WBS Element actual cost total (Cell: C18).
So, if the two separate WBS Element actual cost totals (Cells: C17 vs. C18) both equal $35,004.81, the formula in C17 is a success and has correctly identified and added all the actual costs contained in the PTD-Actual Cost sheet, but if the two separate WBS Element actual cost totals (Cells: C17 vs. C18) both do not equal $35,004.81, the formula in C17 failed for some reason and has not correctly identified and added all the required actual costs contained in the PTD-Actual Cost sheet.
How to Add Total $ Value Formula into an Existing SUMIFS Formula.xlsx
View 2 Replies
View Related
Dec 19, 2008
I currently use the following formula: =workday(H21,H23,J20:J34) which calculates my date based the date entered in cell H21. While testing I realized that I needed to add another variable to the formula for a specific scenario. When my Date in H21 is less than todays date I require another workday formula to be calculated. How can I add this if statement to the existing formula?
View 2 Replies
View Related
Dec 22, 2012
I'm trying to add either a new condition to a formula or modify existing formula, so blank cells can't be taken as a number like 0 Here is the formula =IF(N14>0,1,0)
***This is what I do with formulas with similar problems =IF(COUNT(Y14,A14)=2,Y14/A14*100,"")
unfortunately the above condition its already a condition by itself so I don't know what to do whether to modify condition or add a new condition which I don't know how to do.
View 9 Replies
View Related
Feb 21, 2009
I have a data input worksheet, which uses the following code to fill in the missing zeros when cells are empty.
View 10 Replies
View Related
Apr 2, 2009
I have a very simple lookup formula (eg: ='data'!C1) that I need to copy down about 4000 rows. The difficulty is that I need to miss out 5 rows in between each paste but no matter what I try when copying it down, it throws itself out of numerical order. I have tried to type the formula manually for every row but its taking forever and is bound to go wrong at some point. I have attached a very small example to show what I mean - Imagin this example needing the formula to run down in order up to row 4000?
View 2 Replies
View Related
Jul 23, 2013
I currently have 6 formula (see below) that I want to add another variable for. The formulae add together letters in B1:B15 cross referenced against numbers in A1:A15. The first formula adds together the A's and O's in B1:B15 and cross references them with with cells beginning with 5 or 6 in the same row, A1:A15 down.
=SUM(COUNTIFS(B$1:B$15,"A",A$1:A$15,{"5";"6"}&"*")+COUNTIFS(B$1:B$15,"O",A$1:A$15,{"5";"6"}&"*"))
=SUM(COUNTIFS(B$1:B$15,"B",A$1:A$15,{"5";"6"}&"*"))
=SUM(COUNTIFS(B$1:B$15,{"A";"O"},A$1:A$15,"4*"))=SUM(COUNTIFS(B$1:B$15,"B",A$1:A$15,"4*"))
=SUM(COUNTIFS(B$1:B$15,"A",A$1:A$15,{"1";"2";"3"}&"*")+COUNTIFS(B$1:B$15,"O",A$1:A$15,{"1";"2";"3"}&"*"))
=SUM(COUNTIFS(B$1:B$15,"B",A$1:A$15,{"1";"2";"3"}&"*"))
I want to cross reference each of these with a Gender variable (denoted as M or F, for male & female) in the C1:C15 range. Using the last formula as an example should tell me how many M's or F's in the range C1:C15 had B and began with either 1,2 or 3.
View 1 Replies
View Related
Jan 23, 2014
I have lots of data in the form of names, and i want to extract all rows of data for that name. So if name "john smith" appears in column A 7 times, i retreice 7 rows of data.
My formula is this...
=IF(ISERROR(INDEX($A$1:$C$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",
INDEX($A$1:$C$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))
This works perfectly and it gives me my 7 rows of data if i type in "john Smith" into A10, however, i want one more condition to be met in column C, whereby Column C is made up of either 1, or 0. If its a 0 i am not interested, if it is a 1, i am.
View 2 Replies
View Related
Mar 17, 2009
I need to replace the values in the braces with the values in Column A. Is there an easy way to do this or am I stuck with doing each one manually? There are 92 lines in Column A.....
View 9 Replies
View Related
Sep 1, 2013
I am trying to write a macro that should be able to copy existing row and insert values and formula into a new row just below the row. As an example below:
A
B
C
D
E
001
ADESF
500.00
1001210
EMF
The values in column C and D for the new row will be the same.
View 1 Replies
View Related
Mar 10, 2014
I'm preparing Quarterly Evaluations, and am having an issue because an Analyst has superseded an overall percentage goal. In the attached example, it is the overall evaluation with the 'problem data' in red D19 and highlighted.
I'd like to add a condition that limits the points in D19 for 'responsibility 3' to 150.
=IF(ISERROR(C19/B19)*(B10),"-",(C19/B19)*(B10))
*just a note for clarification, the other two responsibilities are a 'per hour' weekly goal, whereas the 3rd is an overall personal effort divided from the overall team effort.
View 4 Replies
View Related
Jun 24, 2012
I'm currently doing a new worksheet for work
Cell's A1: Commission Per Sale Cell B1 Running total of commission
I would like the running total to continuously incorporate all of the above commissions and this has to be in the column next to the new sales commission.
E.g.
Commission $5 $7 $9
Running Total $5 $12 $21
View 2 Replies
View Related
Mar 21, 2013
I run the following formula down a column of records in row AR
=IF(COUNTIF(lookup!A:A,N2)>0, "Exclude", "Not In Lookup")
I want to add to this formula an additional function that if any cells in column A1 are blank, then do not perform the remainder of the equation.
View 1 Replies
View Related
Mar 31, 2009
--------------------------------------------------------------------------------
Ok i have a formula that i use to convert bond prices, which are in 32nds to numbers that excel can understand. I use the formula:
=LEFT(F7,FIND("-",F7)-1)+SUBSTITUTE(RIGHT(F7,LEN(F7)-FIND("-",F7)),"+","")/IF(RIGHT(RIGHT(F7,LEN(F7)-FIND("-",F7)),1)="+",64,32)
This allows me to type 101-16 in and excel recognizes this as 101.5
My problem is that my data I am copying into the sheet is often in 1/8ths. So the price will come in as 101-272, which means 101-27 and 2/8 (or 1/4).
I admittedly dont understand this formula and just use. Can someone help me tweak it to recognize the third digit?
View 11 Replies
View Related
Jan 26, 2009
I'm currently applying these formulae manually, and also instructing other people to do the same, due to my lack of understanding of macros. Hopefully someone can give me some instruction as to how to do this automatically. I'd like people to be able to open sheets sent as normal CSV's and apply a macro to get them into the right format, changing only rows with data in them.
These are the instructions I'm currently sending:
In cell n1 enter:
full phone number
in cell m2 enter:
=RIGHT("00000000" & J2,8)
Drag m2 to last record
In cell n2 enter:
=CONCATENATE(61,I2,M2)
Drag n2 to last record
In cell o1 enter:
Date of call
in cell o2 enter:
=DATE(MID(C2,7,4),LEFT(C2,2),MID(C2,4,2))
View 6 Replies
View Related
Sep 12, 2009
I am trying to apply named cell from another worksheet to existing formula.
For example I have a formula in sheet2 which looks like =sheet1!A1+sheet1!B1. I then subsequently name sheet1!A1 = Jan and sheet1!B1 = Feb. Thinking that if I apply these names the formula in sheet2 would look like =Jan+Feb. However I get the message "Microsoft Office Excel cannot find any references to replace" when I try to apply the names using Excel menu "Insert - Name - Apply".
View 4 Replies
View Related
Mar 12, 2014
I'm currently using the following formula to return values rounded down to the nearest 2.5: =FLOOR(X8*1.25,2.5)
This works fine but I would like to put in place a system where if the "X8" value is 1 the formula produces a 2.5, and if the "X8" value is 10 or greater the formula produces a 10.
View 1 Replies
View Related
Sep 3, 2013
Currently, I am using countifs to count data within a specific range if it meets a criteria. Now I want to add to the formula. I need to count the cells within the specific range meeting the existing criteria I already have but only if one cell is greater than another cell.
This is what I currently have: =COUNTIFS(Report!$F$4:$F$1048576,">=8/1/2013",Report!$F$4:$F$1048576,"=8/1/2013",Report!$F$4:$F$1048576,"
View 2 Replies
View Related
Feb 28, 2014
I have a file with many different row sizes and after calculating the figure, we will need to add up the total for a particular job. Due to the irregular data on each job (some with only 1 line item where sometimes the job have few line items).
The cell to sum already has a formula for the data.
For example, I already have a formula on Column L to get the figures. After few line items, I will need to add a total to the lines item, also on Column L. I think we can uses 2 sets of data from 2 different column as "pointers", Column D and the formula column itself (but I don't know how to). The columns that I need a sum function will be from Column L to X. After doing the sum, can we at the same time draw a sum line? (single top line and double bottom line).
Am I being "too greedy" ? Imagine if we have more than thousands lines of such data to be added manually and to draw these lines manually.
View 3 Replies
View Related
Dec 9, 2009
I'm trying to write a SUMPRODUCT formula (cell H2 in the attachment) that gives different outputs according to the value of another cell (H1), but the output values are incorrect. I suppose it's because the "else" value given in the IF formula won't be recognized as a formula but as a text.
View 5 Replies
View Related
Jul 11, 2007
I'm currently using a series of SUMPRODUCT formulas to populate a summary page from a large data source. This works fine in the most part, however one part of the summary page calls for a number of different criteria to be counted within the same data range. I'm currently using a work around by having three seperate SUMPRODUCT formulas on hidden rows, and then totaling their answers to give me the result I want, however I wondered if there was a cleaner and better way to do this? An exampel of the formula I am currently working with is below:
=SUMPRODUCT(('Filtered Data'!$A$2:$A$10000="Stock-Out")*('Filtered Data'!$I$2:$I$10000="Open")*('Filtered Data'!$Q$2:$Q$10000="A"))
The final part of the formula is what is causing me the problem - I would like it to look for and count occurances of "A" , "B" , & "C"
View 9 Replies
View Related
Jan 29, 2009
I've got a workbook where sheet "Raw Data" is used to enter audit findings. Subsequent sheet "Analysis" contains formulas to extract quantities and nature of audit findings so that they can be shown on quarterly reports.
When I set up the workbook the formulas on the "Analysis" sheet worked fine. The department has filled the columns with data and now all the formulas are returning #Value! There's something fishy going on here.....
=SUMPRODUCT(--('Raw Data'!$H$7:$H$1000=A4),--('Raw Data'!$K$7:$K$1000=C$1))
View 4 Replies
View Related
Feb 24, 2009
No doubt due to a fundamental lack of understanding my SUMPRODUCT formulae (please see attached workbook) is not working.
Can someone be kind enough to point out the error of my ways? I've tried every possible combination (except the right one).
View 7 Replies
View Related
