Sum Based On Text & Month Criteria

Sep 4, 2007

I am trying to get the sum of some cells (integer varies in column G), but comparing one column content (exact) and dates in a different column.

I tried the following:

=SUM((Tony!G30:G44)*(Tony!D30:D44="Crazy")*(MONTH(Tony!E30:E44)=7))

Column E would contain a date, such as 01-07-07 or 1st July 2007.

In the D Column, keywords such as "Crazy" are concise and standard. However regarding dates, am I better off finding a formula that looks for cell content (Contains "july", as opposed to ="July"), or using a month function (but getting it to work)? How can I do this?

View 3 Replies


ADVERTISEMENT

Sum Based On Month Criteria

Sep 24, 2007

I maintain a table with projects and their respective costs / revenues.

I have a formula that automatically sets the forecast and Year-to- Date periods based on the month and date.

I need to automate the year-to-date sums such that, when the date changes and a new month acquires the YTD status, that the monthly costs/revenue of the projects are updated e.g sum of Jan-Sept in the YTD column(for this month).

A sample workbook is attached.

View 5 Replies View Related

Average Based On Criteria :: Within Each Month

Jul 29, 2008

I have a list of ages of people who cancelled their account. I'd like to find the average age of people within each month.

So column A = age
and column B = cancel date

Let's so for January 2006 I'd like to find the average age.

Logically this is how I see it...

Average Column A (If column B >= 1/1/2006 and < 2/1/2006)

View 9 Replies View Related

Display And Summing Value Based On 3 Criteria Month

Mar 31, 2014

I am looking sum formula to display my data with 3 criteria (display by this month, until this month & until last month) based on header column/correspondents, then in cell L3 as selected month display..

For further information, check workbook attached...

SUM 3 MODELS MONTH.xlsx‎

View 7 Replies View Related

Lookup Data Based On Month Criteria?

Apr 5, 2014

show/display data based on month criteria, with selected month in cell H2 (as start date) and I2 (as end date) my expected result start from cell H7, i called "blue area"..

see my attached workbook..

View 9 Replies View Related

Formula To Have Excel Select Date Of Month Based On Certain Criteria?

Feb 24, 2014

I am looking for a formula that will select a date in the month based on certain criteria. Found the choose function but not sure if I can really get that to work. I basically have a list of clients, with zip codes, restricted days of the week - and would like the system to group them by zip code and select the best day of the month to schedule an inspection but not pick he restricted day. The goal being have zip codes scheduled together - but on a day other than garbage day. Is this even doable?

View 2 Replies View Related

Count Where Text Value In 1 Column & Month Of Dates Meet Condition/Criteria

May 23, 2008

need a formula to calculate the total number of x's in one column (column B, C, E are training types= x) where the corresponding column date falls within a date range. Its to total each type of training done for each month. I have 3 training type columns and a cell that calculates the total number of trainings for the each month:=COUNTIF(F2:F100,">="&DATE(2008,2,1))-COUNTIF(F3:F200,">="&DATE(2008,2,31)).
So now I just need it broken down by training type per month. How many x's in each column for February as an example.

View 7 Replies View Related

Combining Text From Rows Based Upon Current Month Dates

May 19, 2014

I have sheet1 where there is a data.

Column A has dates and column F, G, H have some text with ',' ( e.g. Paper, wall, taps...etc)

I want to now combine text values from Column F ( rows of current month only) and place them in sheet2 in one cell with ',' in between text and not showing same item twice.

Sample book attached ....

View 10 Replies View Related

Import Text Files Into Sheet Based Last Month (name File)

Aug 15, 2014

All my files is in C:UsersmarrecoDownloads All files has name formatt (yyyymmdd) -> 20140814 I need import only last month files

Every day a system plays text files within the (C: Users drake Downloads ) folder.

E.g. I have to import only the files that have the name (yyyymmaa) based on last month.

[Code] ......

View 14 Replies View Related

Returning Text Against Text Based Criteria?

Jul 19, 2014

I need to analyse more than 4500 product of my company. I have data in more then 20 separate excel worksheets, I need a function or a vba code to return the text against my product names.

I my main file i have the names of the products, sales and purchase data and analysis. In other files I have the categories list against those products. In the main file the products are according to brand and categories have different products from different brands grouped together. I want a function something similar to sumif but for text.

Example

What I need
Art no. Product Name Brand Group Category Subcategory
y-123 abc lux hair care shampoo hair shine shampoo
A-123 bca loreal skin care anti acne anto acne soap

Main file
Art no. Product Name Brand Group Category Subcategory Q1 sales Q2 sales Q3 sales
y-123 abc lux 120 110 105
A-123 file with categories against respective products.xlsxmain file what i have.xlsxmain file.xlsxwhat i need.xlsx

Category files

Art no. Product Name Brand Group Category Subcategory
y-123 abc lux hair care shampoo hair shine shampoo

View 1 Replies View Related

Sum Range Based On Text Criteria

Mar 18, 2009

Trying to sum a range based on the criteria of the 1st position within each cell in the range. Cells have data like "A-8", "A-4", for a total of 12 for "A". This is the formula I have that is not working.

=SUMIF(left(C4:P4,1),"A",RIGHT(C4:P4,1))

View 6 Replies View Related

Repeat Row Based On Text Criteria

May 29, 2007

to run a macro or any other methods to repeat the same head rows in my database as followings:

Let's say the main head row is in row 1.

Item names such as Item A; Item B; Item C ....in column A

What I want is to insert the same head row after the list of each item.

Item name (head row)
Book A
Book A
Book A
Item name (head row to be repeated)
Book B
Book B
Item name (head row to be repeated)
Book C
Book C

View 9 Replies View Related

Return Text Based On Criteria

Jun 2, 2009

Example: I have 2 sheets, a pivot and a data sheet. When selecting a different option on the pivot i want information returned from the data sheet (which is explanations of the information contained in the pivot) I need to add 2 criteria points.

View 5 Replies View Related

Return A Text Value Based On Criteria

Jan 26, 2010

I'm trying to create a formula that will allow me to pull test from a list (auto populate if possible). Essentially you will see on the second tab, a list of projects "Cali" for example. I'm trying to find a formula that will allow me to show the Customer and Life Cycle on the first Tab. If possible the Project Name too.

Essentially I want to be able to have all the data inputed into Tab 2 and let Tab 1 condense it into the designated fields. So basically what will allow me to see all of the "Cali" projects, and from that generate the Customer and Life Cycle (and Project if possible) on Tab 1. Keep in mind this does need to be automatic updating, so that as we input more information on Tab 2, it will automatically kick into Tab 1.

View 2 Replies View Related

Sum Based On Part Text Criteria

Sep 27, 2007

I tried to modify a sumproduct formula given previously by Kris which is to sum base on strings, to solve another problem but with no luck. My attempt without success is in BLUE column whereas the correct answers should be that in the RED column. Basically my problem is that if a condition is true, I want to concatenate the values of 4 cells with a space in between them.

View 4 Replies View Related

Function To Fill All Days Of Month To End Of Month Based On Workdays

May 1, 2006

I would like to create a monthly inventory, based on workdays (Monday - Friday)Myrna Larson has a formula that I would like to use with the workday function, but I don't know how to combine them.

=IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))+ = workday

to fit on the page, I need the dates to be from the 1st to the 15th, and 16th to the 31st. I am not sure how to write this either.

View 11 Replies View Related

Delete Rows Based On A Text Criteria

Aug 24, 2009

I am trying to figure out how to have some VBA look down Column 1 and where the Cell's string value = "GRANDTOTAL", to DELETE that ROW and ALL ROWS below.

Can anyone help me out in writing the Visual Basic code?

View 8 Replies View Related

SUMIF With Two Text Based Criteria Using INDIRECT

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

Counting Instances Of Text Based On 2 Criteria

Aug 17, 2006

Need a way to count the number of times a certain bit of text occurs in a range based on a given criteria? Say I've got a column of names and another column with true or false in it (next to each name), how could a do a total of how many times 'TRUE' occurs next to each name?

View 3 Replies View Related

Replace Text In X Columns Based On X Criteria

Oct 17, 2007

I've attatched the workbook here with some dummy values in place of financial figures (We don't base our profit on RAND() formula's )

Essentially, I would like to search for CAN in A and SER in B and if they're found, to alter them to SYD (A) and CAN (2). It's essentially an automatic find and replace, but based on 2 criterias rather than one.

View 9 Replies View Related

Conditional Formatting Based On Multiple Text Criteria?

Mar 1, 2014

I'm trying to set up conditional formatting in a spreadsheet where there are 3 possible correct letters that can be entered into a range of cells and if one of these letters is not entered then I would like the cell to be highlighted.

To be more precise, it's an accommodation spreadsheet and in each cell if someone needs a room for that particular night (each night is represented by a separate column) then a letter is used to indicate what type of room they require i.e. "k" is a king room, "s" is a Suite and "t" is a twin. I don't want to force people to have to select from a list which would be the obvious solution to this as it is simply too cumbersome for the amount of data entry we do.

However i want to ensure that if someone accidentally enters anything other than t,k or s then the cell is highlighted. We work in large spreadsheets covering months of dates so to manually have to find any errors is hugely time consuming.

View 10 Replies View Related

Formula To Count Based On Text / Multiple Criteria?

Jun 27, 2012

I can't seem to get a formula to work. I am trying to count the number of times "EE" shows up in a range only if "CR" is listed in that row within another range. For simplicity Column A has a list with the following values scattered from A2 through A100: CR, LT, ST, DI. Column B has a list with the following values scattered from B2 through B100: EE, EF, EG.

I need to know how many EE's are listed in column B if CR is on the same line in column A.

View 6 Replies View Related

Counting Text Fields Based On Multi Criteria

May 12, 2006

Trying to count the number of fields that meet a criteria base in 3 different columns. Worked on this for few hours but still keep hitting the wall of no right answer. I have attached an example sheet.

Trying to find the Number of fields that are not blank in the range of B9:B28 and have no date (are blank) in range(C9:C28) and have a number that is < 7 in range (D9:D28).

View 3 Replies View Related

Search Text String And Return Value Based On Multiple Criteria?

Apr 8, 2013

Here is an example of the data I get each day Letter order granting Sabine Pass Liquefaction, LLC's et al 4/16/12 request to add an alternate water source etc under CP11-72.Letter order granting Cameron LNG, LLC?s 4/5/13 filing of a request to introduce natural gas or process fluids into the BOG Liquefaction Project under CP12-15.Letter order accepting NorthWestern Corporation's 8/7/12 submittal of revisions to its transmission planning process to comply with the Commission's June 8, 2012 Order under ER11-2932.Letter order approving Public Service Company of New Mexico's 12/7/12 filing of a joint Offer of Settlement with Navopache Electric Cooperative, Inc under ER11-4534 et al. How can I set up my spreadsheet and what formulas can I use to search and return a value for each text string based on the attached table (column B)?

E3a7qta.png

View 1 Replies View Related

Finding Minimum Value In Column Based On Multiple Text Criteria In Other Columns?

Jan 2, 2013

This formula allows me to find the lowest value in column U where column N contains the text "NO".

{=MIN(IF($N$2:$N$10000="NO",$U$2:$U$10000))}

I want to add another condition so that the formula only returns the lowest value in column U where (i) column N contains the text "NO" and also (ii) column F contains the text "YES".

View 11 Replies View Related

Excel 2007 :: Looping Through Text Strings And Copying Based On Criteria

Sep 17, 2012

I have 2 excel files, let's call them 1.xlsx and 2.xlsx (excel 2007)

File 1 is the file where I want data copied into and file 2 is the file I want to copy data from.

File 1 has certain text strings in every say 5th column in always row 2. I want to find those strings in file 2 and if the string is found, go 6 rows down, copy the cell, and paste it into file 1, 8 rows down the text string. this is the code I came up with, but it doesn't work

Code:
Option Explicit
Sub get_data_from_2()

Dim i As Long
Dim j As Long
Dim FinalColumn As Long
Dim RngFrom As Range

[Code] .......

the bolded part gives me an error.

View 3 Replies View Related

Conditional Rank Based On Whether A Criteria Cell Includes A Specific Text String

May 8, 2009

I'm racking my brains as to how I can structure a formula to conditionally rank a value in an array against only those values in the array whose corresponding criteria cell includes a specific letter.

So for example I have a list of 12 values, say 126; 239; 0; 171; 162; 157; 130; 199; 122; 153; 0; 15.
Each of those values corresponds to a heading, say: CDE; DFE; FGE; DFE; ERD; DEA; BDF; DFB; CDE; CEF; CAB; FAB. As you will note some of the headings may or may not be the same and may or may not include the same letters in different orders.

How can I write a formula that ranks in ascending order a given value drawn from the above list (which will be in another cell but which in this case is, let's say, the first value: 126) only against those values whose heading includes a specific character, for example the character C (the character in question will vary and be defined in a specific cell).

As an added complication I need the ranking calculation to exclude any zero values. So in the above example what the formula needs to do is rank the value 126 against a sub-set of the whole array comprising only the values 126; 122; 153; 0.

The answer I need is 2 because, discounting the zero value, 126 is the second highest value.

View 9 Replies View Related

SUMIFS More Than One Criteria And A Month

May 24, 2014

Attached is a spreadsheet. The first worksheet is a summary of the data inputted in the second. I am trying to total the "total spend field depending on whether it is "Extra Works", "Spares & Materials" or "Other" which works fine. I would now like to sum depending also on the month that it was spent.

Order Tracker 2.xlsx‎

View 13 Replies View Related

Month Criteria In Countifs ????

Feb 11, 2010

check out the COUNTIFS function on Data Tables Sheet -> Cell D29 and pls. let me know what's going on. I need is : To calculate the number of items on system data tab which has the "Pending" status and items which belongs to month "Jan-10" under a particular person selected from B 27 cell (Data tables tab).

I solved it by using >=01-01-10 and <=31-01-10 criteria but i don't think its nice idea .... I tried to use AND(function by got failed ...........

View 4 Replies View Related

Search For Info In Sheet 2 Based On Date And Text Criteria In Sheet 1?

Jun 18, 2013

Here is an example of my spreadsheet: Excel example.xlsx

I would like to get all the bid/ask quotes for the different currency pairs in sheet 2 into sheet 1 sorted by the right date and time.

I need a formula which recognize the time + the currency from sheet 1 and search for it in sheet 2 then brings the right bid/ask quote back into sheet 1.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved