SUMPRODUCT INDIRECT & SUMIF Formula
Jul 12, 2006
I have a set of data as attached where the productivity of each staff is recorded on a daily basis.However, the cell in which their name is located on each tab of the day of the month might not be the same. I am thankful that the forum actually provided me with the formula located in cell c15.
1)However, I would appreciate if someone could translate what the formula means as I am not so sure what the formula represents.
2)The reason is I would like to calculate how many training days (TR), Medical Leave(ML) and Emergency Leave(EL) for the month of January for each staff.I tried modifying the formula but it didnt work.
3) Is it possible to use a vlookup function for this solution?
View 9 Replies
ADVERTISEMENT
Jul 15, 2013
I am trying to sum the same data across several tabs but I am using information in the column and row headers to find it. For some reason this formula wont work and I can't figure out what I'm doing wrong.
=SUMPRODUCT(N(INDIRECT(""&Sheets&"!"&ADDRESS(MATCH($A$12,$A:$A,0),MATCH($G$3,3:3,0)),FALSE)))
I'm getting #REF! and when I step through it, it fails when it reaches the Indirect statement.
View 9 Replies
View Related
Aug 11, 2013
Excel - 2010
I have the following formula:
=SUMIFS(FEB!TRANS_Cost,FEB!TRANS_Cat1,CAT_Main3,FEB!TRANS_Cat2,"Computer Items",FEB!TRANS_Status,"Paid")
TRANS_Cost = B:B on FEB tab
TRANS_Cat1 = C:C on FEB tab
TRANS_Cat2 = D:D on FEB tab
CAT_Main3 = "Everyday_Expenses"
TRANS_Status = E:E
In short the formula adds the total cost of anything 'tagged' as Everyday_expenses, computer items, paid. I want to adapt it so that it takes the value from a defined cell such as Month, so that data can be pulled by month.
View 2 Replies
View Related
Aug 24, 2009
a1=good
a2 = 100
b1 = bad
b2 = 50
c1 = good
c2 = 100
would like to sum a2+b2+c2 only if a1,b1,c1 = good.
View 6 Replies
View Related
Oct 11, 2011
I am trying to do a dynamic sumifs that allows the user to use data validation lists to select 5 items to query. This is the easy part.
The hard part is that the amounts range to sum goes across 6 weeks and I want the answer to also take account of another validation input which specifies up to what week the range should be.
I have done a simply if statement in row 8 that flags weeks relevant to input cell B6.
How do I do a formula in say cell A16 that does the sumifs and acknowledges that the user has requested a dynamic range to sum?
Sheet1ABCDEFGHIJK1RegimePost 20052TypeBudget<<<<- Dropdowns3Prod AreaLoans4ChannelRetail5Pro/ReReactive6Week18-May-1178Flags ->1110009Regime 2TypeProd AreaChannelPro/Re04-May-1111-May-1118-May-1125-May-1101-Jun-1108-Jun-1110Post 2005Budget Loans Retail Reactive 36942041320736540011Post 2005Budget Cards Retail Reactive 38423444021726636212Pre 2005Actual Mortgages Tele Proactive 35739840536923740113Pre 2005Actual Loans Direct Proactive 3213872554552254621415Answer16Excel 2007
View 3 Replies
View Related
Nov 16, 2009
The following formula sorts for specifics in the sheet named 200910 in the specified ranges in columns A and D to return a total found in column AB. This works just fine.
=SUMPRODUCT(('200910'!$A$2:$A$1777="Countrywide")*('200910'!$D$2:$D$1777="Claims-All Products"),('200910'!AB2:AB1777))
What I am looking to do, instead of telling excel what sheet to go to, is insert this: =INDIRECT(TEXT(Y10,"yyymm")&"!ab1749") to find the matching sheet name to the date that resides in cell Y10.
These both work separately on their own to return the needed value. How do I put them into one formula without telling excel what sheet to go to (1st formula) and specifically what cell to go to (2nd formula) because the cell location may change and I want to completely automate this?
View 3 Replies
View Related
Apr 20, 2007
=SUMPRODUCT(--(A2:A1000=B1))
Column A has got random numbers, my formula attemts to capture the count of numbers as specified in B1, is there a way I can nest an indired t formula to mention in cell C1 ( > or < ) so it can look for numbers greater than the number in B1 or lesser as indicated
View 9 Replies
View Related
Jul 15, 2006
Can someone help with this formula,
Cell $A$24 = A cell formatted as Month and Year = July06
Cell $B$1 = a date 1/7/06 linked to $A$24
Trying to use the indirect function to ref a sheet called July06 and other ranges here a example of one range =July06!$D$2:$D$247
This is what I've got
=SUMPRODUCT(--(INDIRECT(TEXT($A$24,"mmmmyy")&"!$D$2:$D$247<="&$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!$Y$2:$Y$247>= "&$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!$C$2:$C$247="&$A2)))))
View 9 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
Dec 18, 2006
In book3.xls, worksheet 'statistics_by_class_day', how can I rewrite the formula in C7 such that the count is based on number chosen in A3 and C3?
=SUMPRODUCT((INDIRECT("'attendance"&$A$3&"'!C:C")=$A$7)*((INDIRECT("'attendance"&$A$3&"'!I:I")<>0)))
In the formula, I:I refer to Day 4 of worksheet attendance9 (ie Column I), can I use INDIRECT() by referring to C3?
View 2 Replies
View Related
Oct 15, 2013
I am trying to use the sumif and indirect function together but I keep getting an error and I'm not sure why.
Actual formula:
=SUMIF('SWG OR'!B:B,A168,'SWG OR'!E:E)
Using the sumif/indirect function, I have the following:
> Cell B5 contains the name of the tab "SWG OR"
=sumif(indirect(B5&"!B:B",A168,B5&"!E:E"))
View 2 Replies
View Related
Dec 9, 2013
I am trying to stream line the recording of time tickets . I would like to create a Summary sheet that shows the amount of hours each worker has done based on that particular day. The trick is I want the summary sheet to be automatically updated when the each individual time ticket sheet is completed. I would also like to be able to drag the formula across a row so I do not have to recreate the equation each time a new time ticket sheet is completed. Each time ticket sheet is its own sheet and is identified by date. Each time ticket ticket sheet is identical and is already created to so all you have to do is click on that the sheet reflecting the date and fill in the information.
This is the equation I have created but keep getting #VALUE!
=SUMIFS((INDIRECT("'"&MONTH(J$8)&"-"&DAY(J$8)&"-"&YEAR(J$8)-2000&"'!$L$11:$L$34")),(INDIRECT("'"&MONTH(J$8)&"-"&DAY(J$8)&"-"&YEAR(J$8)-2000&"'!$B$6")),('Data (2)'!J$8),(INDIRECT("'"&MONTH(J$8)&"-"&DAY(J$8)&"-"&YEAR(J$8)-2000&"'!$C$11:$C$34")),('Data (2)'!$I$9))
The sum_range is Manhours from L11:L34 for each time ticket sheet. The criteria_range1 is date in cell B6 listed on each time ticket sheet, criteria1 is date listed on cell J8 of the summary sheet . The criteria_range2 is names from C11:C34 for each time ticket sheet, criteria2 is name of person on cell I9 of the summary sheet.
Each sheet is labeled as 8-19-13, 8-20-13, 8-21-13, and so on. Summary sheet is labeled as Summary
The table that I am using to record the data on the summary sheet has the date increasing by one day on row 8. The names are vertically listed on column I.
View 1 Replies
View Related
Apr 23, 2008
I have a need to sum values in the right column of a 2 column table based on multiple criteria of the values in the left hand column. The table has 500 rows.
The two criteria are:
1 - that the cell contains a specific text string ("product a")and
2 - that the cell contains a text string for the month in question ("Jan")
The other limitation I have to work with is that the formula is in a different workbook to the source data. I have a worksheet that lists the external workbook/sheet and have been using INDIRECT in the DSUM.
I read through the examples here:
[url]
Basically they recommended DSUM as the most efficient.
I can get the DSUM working, but it needs the workbook to be open before it can evaluate the expression and therefore I get an error when I select to update values upon opening the workbook.
So in summary I wondering if anyone can show me how to write a SUMIF formula based on the above criteria.
View 9 Replies
View Related
Apr 4, 2014
Basically I have an equation:
=SUMPRODUCT(G9:G11,H9:H11)/SUM(G9:G11)
It's just a simple percentage calculator for my purposes and works fine as is. However, I want to make it dynamic whereby from a user input the length of the array will increase or decrease, e.g. G9-G11 will become G9-G12 if there are four rows occupied with data. For all my other SUMming equations I have solved this and even came up with what I think is a perfectly valid solution for this one. The one problem is that it doesn't work when combined into a single equation.
My solution for the upper part of the fraction is this:
"A1"=SUMPRODUCT(INDIRECT("G9:G"&MIN(ROW(G9)+A8-1)),INDIRECT("H9:H"&MIN(ROW(H9)+A8-1)))
Where the user inputs the number of rows used in A8.
The solution for the lower part of the fraction is this:
"A2"=SUM(INDIRECT("G9:G"&ROW(G9)+A8-1))
[code]....
View 3 Replies
View Related
Feb 11, 2012
The formula is =SUMPRODUCT((Group="A")*(Project_Description="Long Term")*(Profile="B")*(INDIRECT(B9)="x"))
B9 through M9 have header text Jan_2012, Feb_2012...Dec_2012. Basically my aim is to get the above formula working before I drag it across so that the named ranges get picked up automatically from the headers.
Jan_2012 thru Dec_2012 are dynamic named ranges using INDEX (and not OFFSET as someone mentioned OFFSET is a volatile function).
The above SUMPRODUCT formula is giving me a #REF! error for the (INDIRECT(B9)="x") part. I know that because when I replace it with (Jan_2012="x") it works fine.
I realized while writing this that it may be because the named range in Jan_2012 is not the same size as that of other arrays. [but it is the same size - I've re-confirmed just now]
View 5 Replies
View Related
Oct 21, 2008
ColAColBColC
1 FA4,000.00 10%
2 CB1,500.00 15%
3 FA7,500.00 15%
4 AA700.00 5%
5 JG250.00 5%
6 FA1,200.00 5%
7 AA900.00 5%
8 CB175.00 15%
9 FA2,000.00 2%
At the foot of the spreadsheet I would like to show the total of the calculation
Col B * Col C, if Col A shows a value of "FA"
Having veiwed similar posts, I believe SUMPRODUCT may be the correct formula, but I cannot seem to achieve the result I am looking for.
So in the example the desired result is 1,625 B1 * C1 + B3 * C3 + B6 * C6 + B9 * C9
View 9 Replies
View Related
Feb 26, 2009
How can I write a conditional sum formula that will skip a criterion when it is left blank?
I was able to make this work:
=CHOOSE(COUNTA(B:B),SUMIFS(F1:F4,D1:D4,B1),SUMIFS(F1:F4,D1:D4,B1,E1:E4,B2))
where column B has the criteria.
I was wondering if there was a better way using Sumif or Sumproduct.
View 9 Replies
View Related
Aug 23, 2009
in colum A i have a list of dates, in colum B i have a list of amounts. what i need to get is the sum of the amounts where the dates in colum A are between the dates in cells C1 & D1. Tries sum producst but all I get is the total sum of colum B irrelevant of the date!
View 9 Replies
View Related
Oct 29, 2008
i would to combined these together
can this be done
View 2 Replies
View Related
Jan 6, 2009
I have 4 columns, A B C and D. They are a mix of formats.
A= Name (text)
B= Date
C= Code (text)
D= Number
I need a formula that gives me the total from column D, provided conditions for columns A, B and C are met. The date condition is month only, so I'm using MONTH(1) for January etc.
I can find a formula if there are only three columns total (using SUMPRODUCT), but not 4.
View 14 Replies
View Related
Mar 10, 2009
I have a list of names and corresponding costs charged for each entry. The names appear multiple times for multiple costs charged, but contain any combination of characters after their name.
I am looking to get the sum of all entries for each name. I have tried the following formulae, but it does not return any information:
=SUMIF('Jan line items'!E:E,(CONTAINS:A:A),'Jan line items'!F:F)
View 3 Replies
View Related
Jul 9, 2009
I'm trying to do here is if a layaway has been paid off then there will be a tax amount in column C. IF there is tax in column C and column A has the word layaway (or the word way) in it then I need to find out how much the total of the layaway $ amount was. Meaning, to figure out how much the tax is for.
For example:
If the text in Column A contains the word way AND column C contains a $ amount then return the amount that the tax would be for. Of course this would be an accumulated amount for all of column C. So, if Layaway is in column A and $3.13 amount is in column C then it will return $50.00 as the original selling price. Tax here is .0625 %.
View 2 Replies
View Related
Oct 9, 2009
I need combining sumif & sumproduct. I have attached a file which explains what I need.
View 2 Replies
View Related
Jan 20, 2010
From some fleet data i am trying to total any journey which took place between two times.
The time format is hh:mm:ss. I want to total the miles travelled in each trip between 5pm and 5am for certain vehicles
Vehicle Registration is in colum A, the times of the journey are column B and the miles traveled are column D.
=SUMPRODUCT(--(A2:A100=LG52RYT),--(B2:B1000=NOT Sure???, D2:D1000)
Would I use the above formular? And what would i put in the section i have marked "not sure"?
View 10 Replies
View Related
Dec 8, 2010
I need to convert the following formula to a Sumproduct so the link will not be broken when I close the linked workbook:
=SUMIF('Z:WUTDaily Report[2010 Monthly Rpt - CPM - Thru November only.xls]2010 Expense'!$E$231:$E$332,"M&R Parts D",'Z:WUTDaily Report[2010 Monthly Rpt - CPM - Thru November only.xls]2010 Expense'!AE231:AE332)
View 4 Replies
View Related
Jan 19, 2009
I need to add amounts in column B, based on column A’s account numbers, so I want to add only account numbers, say 17101 & 17201 and nothing else.
View 9 Replies
View Related
Jan 30, 2009
I have a daily tracking sheet. I want (off to the right) to be able to enter start/end dates and have it sum the total grossage for JUST those dates alone. Which function do I use?
http://s401.photobucket.com/albums/pp94/nmweir/?action=view¤t=untitled.jpg" target="_blank">http://i401.photobucket.com/albums/pp94/nmweir/untitled.jpg" border="0" alt="Photobucket">
direct link? :
http://i401.photobucket.com/albums/p...r/untitled.jpg
View 9 Replies
View Related
Jun 17, 2009
I am using the SUMPRODUCT formula as a SUMIF with multiple conditions in the following format:
SUMPRODUCT(-(Range1=CellReference),-(Range2>0),Range3/Range2)
THis works with or without the 2nd condtion, but I thouht the "greater than 0" bit would eliminate the inclusion of 0's; however, the same #DIV/0 error occurs with or w/out that ">0" condtion as the 1st OR 2nd argument.
I speculated that b/c the final argument was dividing each paid of cells in either range instead of a typical sum, this caused the divisor error.
View 9 Replies
View Related
May 2, 2007
I am using the SUMIF formula to sum all the cells that meet a certain criteria and at the moment, this works fine. Now I want to extend the criteria to 2(3 later) conditions. I've read that the SUMIF function only handles 1 criteria. Is this correct? If so should I use SUMPRODUCT or DSUM? I eventually will have to check about 2000 cells in a workbook. Sample work book attached: where I need to total the price of all the items that are shoes and are red in colour. A pivot table beckons in the future, but for now I need this in a workbook format.
View 3 Replies
View Related
Jan 27, 2008
Im Trying to use a formula of countif or sumproduct
so the formula will read the range of cells from A2:A30 to see if the word Hydro is in any of those cells, then if the date range from Cell B2:B30 is from range =>Dec-01-2007 to =<Dec-31-2007, then it will count 1.
[Code] ........
But this formula doest count.
I've tried using a multiple if count if with arrays and sumproduct. I really want it to count the ammount of time the work hydro is used during the month on december .
View 3 Replies
View Related
