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 .
Basically the sheet 'transfers' has a column named type and one named in/out, the current formula I have does a logical test on both of these and then if they are both true takes the cost of it from a column named cost. This works fine for single rows. However on another sheet I am trying to use the above formula but there will sometimes be more than one entry for the same date on the sheet 'transfers' and all the same dates that meet the logical test's conditions need to display the sum of the cost on this other sheet.
I am trying to get a SUMIF formula to work where the following: - RANGE = Column N:N (These are all a list of dates) CRITERIA = Cell C20:D20 (These are the dates i want to look at) SUM_RANGE = Column P:P
Basically, I want to SUM all the numbers on Column P where the date in Column N falls between and including the dates in Cell C20 and D20
In attachement, I want to show in the YELLOW cells the SUM of Column P where the Date in Column N is between the Dates in column C and D.
I am working on a report for work with the following formula:
=SUMIF(A5:G8,AND(between 1-1-13 and 1-31-13,"soft cost"),G5:G8)
my formula is "IF A5:G8 IS BETWEEN JANUARY 1st 2013 AND JANUARY 31st 2013 AND ALSO IF IT IS A SOFT COST THEN GIVE ME THE SUM OF THAT ROW"
Pretty much column A contains different dates and column C indicates whether a cost is a "hard cost" or a "soft cost". If the date is within the month of January AND if it is a soft cost, I need the dollar amount in column G summed-up (must meet both criteria - January and soft cost).
I am trying to sum multiple columns of data by a sumif based on a criteria in column C, the columns are weekly dated, and I wish to match columns by offset this initial column with a start date and end date and sum columns in between, I have been getting close but only returns one columns values?
SUMIF(FilterCol,$C17&I$9,(StartCol:EndCol)) FilterCol is column for criteria match Start Col is name range - OFFSET(FilterCol,0,'Summary totals line groups'!$G$4,1,1) - End col similar, (G4 is a match date to find column ref)
I am trying to do a sumif off all dates that fall into a specific year. I know I can do it by either adding a column in either of the sources to get the translated data but I was wondering if I can get this to work by it recognizing the format within the formula only.
I have a report I am attempting to populate with data from a pivot table in another worksheet. Column A holds all the reference numbers (primary key), column B contains various start dates, and I want column C to contain all the payments made since the start date for each reference number.
The source data is a pivot table with Row = Reference number, column = transaction date, values = transaction amounts. This is an extremely large table, as I'm processing data from almost 1,200 cases, which each have around 20 payments spread over the last year, on completely random days. What I would like to do is build a formula in my report which looks up the records for the reference number from column A, and then adds up all the payments which have been made after the date in column B (and ignore any payments in the table which are before that date).
And to make things more complicated:
if an error is generated, it needs to return as 0, not #N/AThe report has the dates in UK format dd/mm/yyyy, but the pivot table has the dates in SQL format: yyyy-mm-ddThe pivot table is connected to a SQL database via ODBC and has to refresh every time it is opened.
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 %.