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].....
Attached file should be self-explanatory.
I want to be able to set sum range using lookup or match.. For example if I change B2 value to Nov-13, I want it to sum column K.
I set my range as follows :
[Code]....
This gives me two ranges with multiple columns of data, which should work fine.
Then try to gather my data as follows :
[Code] ........
But this DOESN'T work - it seems like SumIfs doesn't work with a range with multiple columns... ?
The code DOES work if the rng2013/rng2014 ranges have only one column of data instead of more...
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"))
I have array formula {=row(indirect("1:5")} which is result to {1;2;3;4;5} and it look like a range.
And i want to insert this into formula COUNTIF at the range section
i'd try it, but it didnt worked.
(its look like insert array formula into range section of a formula)
I have a large metrics spreadsheet that has thousands of formulas using SUMPRODUCT. We are using Excel 2007/2010 now and I wondered if there was a way to convert these type of formulas to use the SUMIFS or COUNTIFS. I can do simple ones with 1 or 2 criteria where its AND, but then there is a combined OR and AND like this one has I cannot get them to work right, am I stuck with using SUMPRODUCT? My hope was to speed up the overall calculation speed. Note: The data spreadsheet 'Sheet2' contains 64555 rows, I write these formulas using VBA so that the range is finite to the actual data range is why you see 64555.
=SUMPRODUCT(--('Sheet2'!$J$3:$J$64555=$A6),--(('Sheet2'!$I$3:$I$64555="HOC")+('Sheet2'!$I$3:$I$64555="MGN")))
I have an issue with SUMPRODUCT. My data is the budget for different items as follows
Column C has criterias such as Payroll, Expense or Fringes.
Column D has criterias such as "32", "43"
Column E to P are the numeric value (budget) by month (Period 01 to Period 12)
I am trying to create a formula that, for a given month, will sum the budget according to specific criteria.
For example, I want the value of the budget for "Payroll" + "32" for Period 1.
I managed to do that with the following formula:
E18 = 1
E16 = Fringes
E17 = 32
"=SUMPRODUCT((INDEX($E$4:$P$12,0,MATCH($E$18,$E$4:$P$4,0)))*($C$4:$C$12=$E$16)*($D$4:$D$12=$E$17))"
I used INDEX/MATCH so that the range into which the sumproduct will look for data to sum is variable and depends on what I want (I can easily change E18 to any period for which I would like to see my budget).
Now, I would like to take this to the next level and ask for a range of a column. For example, I would like to know the value of budget for "Payroll" + "32" for Period 01 + Period 02 + Period 03. How can I do that? I tried adapting the INDEX/MATCH but got no results.
Test on SUMPRODUCT.xlsx
I'm using Excel 2010. I have a spread sheet with sales data covering 3 years and multiple customers. I was able to create formulas such as this to calculate the figures for the entire sheet (all customers) by year.
=SUMIFS(J2:J12904,E2:E12904,">12/31/11",E2:E12904,"<1/1/13").
I believe that in order to use filters that show this date for a specific customer I need to convert this to a SUMPRODUCT formula, I've tried this multiple times and had no luck.
I am trying to do a formula that will add two different sums and so far I have this:
={SUM(IF('FAC Open POs'!$F:$F='Cash Waterfall'!$B6,'FAC Open POs'!$V:$X))}+SUMIFS('Cash Report'!$O:$O,'Cash Report'!$S:$S,'Cash Waterfall'!$B6,'Cash Report'!$T:$T,'Cash Waterfall'!G$4))
But I keep getting an error. I know that the formulas apart work, but I keep getting an error with trying to use an Array formula like this.
I am trying to do a sum of data that contains three requirements - The country has to match either the USA or Canada and then it needs to pull for only a certain month (i.e. Jan, Feb, Mar) and then for that month only pull either Airfare, Hotel, Per Diem, or Car Rental. Basically looking for a way to Sum only the specific category for a specific time frame.
View 9 Replies View RelatedI have a report for which I would like to set up a Quarterly summary section. There are 6 working teams listed in my table and I have created two cells with data validation to allow me to specify which team and for which quarter I would like to pull data. My intent is to have a formula in one cell which can extrapolate the quarterly team information based on the values in the data-validated cells.
My worksheet is set up so that:
The data table (tblD) contains metrics for all teams for the full yearTeam names appear in Column A of the table and the reporting month appears in Column BCell M10 has data validation so that the only choices are: Q1, Q2, Q3, or Q4Cells X1:X4 contain the quarterly values mentioned aboveCells Y1:Y4 contain array references, so that Cell X1 = Q1 and Cell Y1 = {"January", "February", "March"}Cell N10 contains a VLOOKUP which returns the appropriate array from Column Y based on value in Cell M10Cell L11 has data validation so that the only choices are the 6 team names
Now I was able to get quarterly information by manually typing the 3-month array into my formula like so:
Code:
=SUM(SUMIFS(tblD[TU5],tblD[Team],$L$11,tblD[Month],{"January","February","March"}))
BUT! When I try to use a formula which points to the VLOOKUP cell (N10)--thus allowing me to make the display dynamic--the result is always 0.
Code:
=SUM(SUMIFS(tblD[TU5],tblD[Team],$L$11,tblD[Month],$N$10))
While inspecting the formula in the formula bar, I noticed that the values listed in the array appeared with double quotes (i.e. "{""January"",""February"",""March""}"). I tried every permutation of typing the array in the lookup cells I could think of, but none would allow my formula to properly calculate the total I needed.
{"January", "February", "March"}
{January, February, March}
January, February, March
Although I don't often utilize arrays in my formulas, I do employ them from time to time. I'm hoping there's some factor I might have overlooked which keeps my formula from working. is there an issue preventing array references stored in cells from being used in formula?
I have sheet 1 which has titles set up in column A. These titles are pulled from Sheet 2 (same column). I also have a date listed at the top of sheet 1 (in the same row as dates in sheet 2, but sheet 2 contains multiple columns for each quarter). I would like to set something up where if the date in sheet 1 matches the date in sheet 2 it pulls the corresponding values from the named rows in sheet 1. I tried using sumproduct but its not working b/c it wont take TEXT. Plus I want it to search across all of the columns, not just one.
Below is sheet 1 and I would like to be able to pull 1Q08 values from sheet 2 (which contains the values for every quarter). This is my formula (the first part is the date, the second is row titles, the third is the value (although its only pulling from one column, it s/b pulling from all) :
=SUMPRODUCT((Sheet2!B1:B6500=sheet1!B6)*(Sheet2!A1:A6500=Sheet2!A7)*(Sheet2!BA1:BA6500))
Is there someway to fix this, it would be a huge time saver.
--------------------------------------
1Q08
Revenue
Direct Costs
Gross Profit
Indirect Costs
Stock Based Compensation
Restructure Charge ....................
I have two lists of values in ascending order. Column A is an identifier and is always one more than the previous. Column B is ascending as well, but there is never any guarantee that it will increase from one row to the next or how much it will increase when it does.
My index formula looks like this: =INDEX(A2:A21,MATCH(D9,B2:B21,1)+1)
In this example, if D9 = 1 then it needs to return 6 but instead returns 10. How can I make this index for my purpose? Or should I be looking for a different function?
I'm trying to use the sumproduct formula to query 3 columns: I want it to look in column E for either a "P" "C" or an "A", multiply the corresponding numbers in column D and F, and put the sumproduct total in cell C5, D5 and E5 for each formula below. The problem is that I keep getting #VALUE! result. It's because some cells in the range have #N/A in them, but I can't control that.
Here is my formula:
=SUMPRODUCT(--(E9:E70="P"),D9:D70,F9:F70)
=SUMPRODUCT(--(E9:E70="A"),D9:D70,F9:F70)
=SUMPRODUCT(--(E9:E70="C"),D9:D70,F9:F70)
How can I use this formula if for example cells D15-17 have no values or are #N/A?
I want to fill an array from values in range A1:H10. I want to fill the array with all rows in range and only columns B,C and E. I have the code below so far using index function.
Is there a more direct way to select all rows from desired range to avoid the need to create an array of rows from 1 to LastRow and then use Application.Transpose(RowsArr) (in red) inside Index()?
Adding condition to SUMPRODUCT causes result to go up? (2007). I have the following formula:
View 5 Replies View RelatedI am using this to count how many times a particular number appears across numerous sheets, My sheets are in a Named Range MySheets....
=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A30"),A2))
This works great.
What I now need to do is to find ALL matches and return B1 from those sheets..
GHIJKLM1Countif ResultFirst ResultSecond ResultThird ResultFourth ResultFifth ResultSixth Result25AppleBananaPearMangoOrange31Banana43BananaMangoOrange
I have played around and the closest I can get is with this...
=IF(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A30"),$A2),INDIRECT("'"&MySheets&"'!B1"))
This as you may know only returns the result B1 from the first sheet within MySheets.
SUMIFS formula where it calculates multiple columns as SUM RANGE
Currently I've got this formula:
=SUMIFS(INDEX(Link!$C:$N;0;Mth);Link!$A:$A;LOGIC!B$82;Link!$B:$B;LOGIC!D$81)
The above mentioned formula works great, if I use it for one specific month, but not if I want to have my figures Year to date.
If I want to calculate the sum of multiple columns, and the sum is defined by the month I choose. How should the formula be?
For example: If I choose month 5, I want to calculate column C up till and including G (so only the first 5 columns), and if I choose month 8 it should calculate the sum of column C up till and including J.
I’m trying to use the SUMIFS function in Excel 2007 to evaluate the following formula:
{=SUMIFS(range to sum, range to evaluate, evaluation criterion)}
The range to sum is A1:A10, the range (dates) to evaluate are in B1:B10, and the evaluation criterion is that the date is before 31/10/1999.
So my formula looks like this:
{=SUMIFS(A1:A10,B1:B10,”<31/10/1999”)}
This works fine, but how do I refer the 3rd argument in the function to a date in a particular cell rather than typing in the date specifically?
MR EXCEL EXAMPLE.xlsx
I'm trying to create a sumif or sumifs function that will add numbers from two columns. I've attached an example (semi populated as needed to get rid of some personal data so ignore the ref notes). On the Category Worksheet I'm trying to get column D to sum info from the March Worksheet. I would like the function in the Category Sheet to add Columns F and Columns H from the March Worksheet if they correspond to column A22 on the Category Worksheet i.e, the result of the data I've left on this example sheet should be $20....... sum of F = -130 + sum of H = 110
On the attached work book I have 2 sheets on the total minutes sheets I need to run either a VBA or sumif formula that looks at all the Init columns and if it finds that name then it sums the duration cells corresponding with that name.
Picking log-Rev6.xlsm
I need to multiply an array of logical results ( returned as {1,0,0,0,1,0 et.}) with a text array (a reference column) and return the text in the reference column in case the value in the logical array is 1.
View 3 Replies View RelatedIs it possible to use Sumifs to calculate multiple columns i.e. if a summary were able to sum Part No. 742810 O-rings for March it would arrive at 24 O-rings and 20 of the same for April. I tried it using
=SUMIFS(Issues!$F$2:$F$6,Issues!$B$2:$B$6,B8)
(Example) but it seems that Sumifs may have restrictions on the number of columns.
******** language="JavaScript" ************************************************************************>
Microsoft Excel - Parts Distribution.xlsm___Running: 14.0 :
OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutL2L3L4L5=ABCDEFGHIJKL1Parts Description Part No.EquipmentMar1Mar5Mar12Mar30Apr6Apr17Apr27May3
[Code] .........
I'm using the vlookup function to pull numbers off an array. Is there a way that I can get the vlookup function to pick the smallest number greater than or equal to my Look_up value. If not is there a function that can do that?
View 9 Replies View RelatedI am trying to do a SUMIFS from a chart with duplicate values in rows and columns.
Data looks like this:
So this is basically sales of different products (columns) on different routes (Rows).
I want to add up a total for all the same kind of products eg. Magnum for each route eg. Local Top End.
There 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