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!
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 %.
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.
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.
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.
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 .
-I have 3 relevant columns, A, B and C with 999 rows -Column A is a status indicator "Yes" or "No" are the only options -Coumn B a list of vendor names, which requires the wildcard example:"*Verizon*" -Column C is the data range that needs to be summed
Right now i have this: =SUMPRODUCT(--(A2:A999="No"),--(B2:B999="*VERIZON*"),C2:C999)
But, it seems sumproduct does not allow wildcard matches within it's "--" arrays. I am willing to try something else if possible, but I lack the excel knowledge to answer this myself.
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?
I have an array that contains order numbers, tracking numbers and shipment costs. I want to get the total value of the shipment cost per order. the problem is, there are some duplicate shipments (ie same tracking number) and I don't want to include those. I can't delete the duplicate entries from the database for reasons I won't go into here.
so I tired to use a formula like =SUMIFS(C:C,A:A,A1,B:B,B1) A B C 11462046 CJ225083125US 10.51 11462051 CJ225082247US 17.04 11462046 CJ225083125US 10.51 11462046 CJ225083564US 22.40
the formula doesn't work (won't even let me enter it) but if it did, it should give a result of 32.91. it would add C1 and C4 (but not C3 because even though A3=A1, B# also equals B1 and that is what I don't want to add.
I think maybe a sumproduct formula is what is needed but the negative criteria is throwing me for a loop.
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!
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.
The problem I am having is that the sumproduct and offset funtions used to ignore hidden rows are considered "volatile" and force a recalculation. Moreover, I have this fomula pasted 100+ times and this dramatically slows things down even when I turn autocalculate off. Is there another way to accomplish a sumif with a user defined custom function in VB? I really need to have the ability to do a sumif that ignores hidden rows and is not dramatically slow.
I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.
In my workbook I have about 20 sheets (less in the attached sample), and on most sheets I’d like to query data for each day and find an average value based on the time and then copy that result into another sheet. I’m not sure if I’ll need 2 macro’s for this or if one can be used.
Here’s a quick description:
Assuming that I am currently in the sheet I want to run the macro, then I will either input “1:45” or “0:45” into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. I’m flexible
If I input 1:45, then I’d like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....