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 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.

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)

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 %.

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?

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.

[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 .

I am trying to extract data all countries excluding Spain and Portugal and also exclude data for certain product classes (for example excluding Products X, Y and Z)

I have written the formula below which picks up data for certain criteria I have concatenated in tab "raw data" in column A:

Notes: Cells B7 and D3 are used match criteria in Raw Data A:A Column W is the data that I need to extract i.e the numbers Raw data I:I is a list of all countries

However this does not exclude Products X, Y and Z, only excludes countries Spain and Portugal.

I wondered if there was an easier way to create a formula?

-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 attached spreadsheet has a "master" workesheet in which I enter customer info, salesperson info, and date. The totals spreadsheet automatically calculates number of sales, contact value.

I need to modify the following formulas to only calculate the data within a date range shown in 2 cells.

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?

I'm using Excel 2003. The sumif function will not allow me to ignore hidden rows in my data set that have been autofiltered. I tried using the following formula and it worked:

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 want to determine the number of values SUMIF/SUMPRODUCT functions used/checked to arrive at the answer, i.e. 95 for row 2 and 116 for row 3. A function that can give a 3 and 2 respectively.

Example:

Results for Row #2 gives: 95 with 3 values picked up under "Rate"

Results for Row #3 gives: 116 with 2 values though three places were checked up under "Rate"

How can I count the number of these values referencing at the "Rate" like in the functions used.

I managed to use SUMIF/SUMPRODUCT functions to calculate totals based on "Rate" in row one.

I tried COUNTIF function, but allows only field/heading.

I'm looking for a formula for use in powerpivot, that can convert the following formula (I do not wanna use any VBA, only generate a caluculated column in powerpivot, based on a formula)

[Code] .....

The formula checks for 3 different values, and returns a status code for each. If no match is found it returns "no match".

The formula looks for the criteria (1234 or 1857201 or 1857206) in column2 (item) and writes a status code in column3 (output). At the same time it also checks for simular order numbers (column1). So the same status code is present for all order-number rows. (If a match in column2 / item is found)

See the excample in regular excel here: [URL] ....