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.
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.
Tab 1 has columnar data with column A containing locations; however, each location may be listed multiple times on different lines - once for each piece of equipment at that location. Tab 1, column B has a piece of equipment listed. Tab 1, column C has a price associated with the piece of equipment in column B.
Tab 2 contains contains columnar data with column A containing locations. I need to populate Tab 2, column B with the sum of all equipment associated with the location in column A.
I have used VLOOKUP to try and find matches for the lookup_value in Tab 2, column A, then return the value of the price from Tab 1, column C.
Problems have been that it returns only the first instance of the location data, not a sum of all. I know the formula is missing something, but cannot ID it.
Formula as used:
B4 is the column on Tab B that contains all of the locations (lookup_table) Tab 1 I4 thru AI461 is the table_array where all locations are listed 3 is the col_index_num indicating that the price is in the 3rd column over
Column B has the "date" Column D has the "time" Column E has the "field" then in Column F is "VIN" and Column G is "HOMI"
Then I have another area on my page that I need to redistribute data.
I need a vlookup, sumproduct, or something that will give me the data I want. here is what i am looking for:
R138 through AE300 is the data area. Column R has the "date" Column T has the "time" Column V and W are "field 1" Column X and Y are " field 2" Column Z and AA are "field 3" Column AB and AC are "field 4" Column AD and AE are "field 5"
I want to be able to put a formula in the field areas V138:AE300 that will find the "VIN" and put it in my new area and my "HOMI" and put it in the new area.
Is this impossible? Or is this just wishful thinking? If I need to give anymore info let me know. I cannot add any software at work so I can't show you the data I am talking about, unless i can copy and paste the sheet to show you.