Close Window
Free Ebook
Get Your Free Excel
ebook!
Top 15 Excel Tutorials from TeachExcel.com
Instant Access!
E-mail:

Sumif For Multiple Criteria With Text String

Hi,
I am trying to use a SumIF statement (in excel 2003) for multiple criteria where one criteria is a string. I'm trying to sum column C if column A contains the word "car" and column B contains any text with any part of the word "blue". I've tried sumproduct, but I can't get it to recognize my string ("*blue*"). Thanks! =)


Free Excel Courses



Similar Excel Tutorials


How to sum values that equal one of many potential criteria; this is basically summing with an OR condition. This ...

The SUMIF function allows you to sum values based on a single criteria. This function works in all versions of Exc ...

How to count cells that match text at the start or the end of a string in Excel. If you want a fuller explanation o ...

The SUMIFS function allows you to sum values that meet multiple criteria across multiple columns. Each value that ...


Helpful Excel Macros


- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt

- This free Excel macro filters data in Excel based on multiple criteria for one field in the data set. This macro uses t

- This free Excel UDF (user defined function) returns the first word from a cell in Excel. This extracts the first word f

- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose

- This Excel macro filters data in Excel without the filter "arrow" appearing in the column that is being filtered. This

Similar Topics







I have cells in a column, some colored yellow, some not. I am trying to use
the SUMIF function to sum only the cells in the range that are colored. Can
this be done? Can I enter something in the "criteria" part of the formula
that can do this?

=SUMIF(D3:D13,"criteria",D3:D13)

I also tried to use the CELL function's color feature, but I couldn't get it
to work right. I don't know how to get Excel to recognize if a cell is
colored in a formula.
=CELL("color",cell)

It might just be that I don't know what this means in Help:

"color" --> 1 if the cell is formatted in color for negative values;
otherwise returns 0 (zero).

Can anyone help?



I am trying to sum all numbers <0 using the SUMIF formula in Excel 2003.

FORMULA: =sumif(range,criteria,sum_range)

I got it to work, but I have cell ranges to total that are not next to each
other (such as A5:A15 and C5:C15).
When selecting the ranges for the formula, Excel writes them A5:A15,C5:C15

because of the comma (,) between the cell ranges Excel is reading the A5:A15
as the range and C5:C15 as the critera. How can I get this formula to allow
me to select multiple cell ranges?

All help is appreciated.

Thank you.



I need to find data in two different formats within a column

Examples

Webb Christopher


Greer Nancy

I need to find cells with text that have more than one space between the first and last names; and cells with text that have only one space between the first and last names.

I am fairly new to excel. I can find the space after the first word but cannot tell it to find ones that say have a space then another string of text.

Thank you for your help!



Is it possible to apply a filter to multiple sheets within the same Excel workbook, using the same filter criteria?

For example, suppose you have tables of data on 3 different tabs, all having the same headings. Suppose one of the headings is "gender". If you apply a filter to the table on Sheet 1, to only show the rows containing the word "female", is it possible to have the tables on Sheets 2 and 3 automatically update to only show the "female" rows? Or would you have to manually update the filter criteria in Sheets 2 and 3?

I hope this makes sense. Thank you in advance for any help.


I would like to copy a small table from Word into one cell in an Excel
worksheet. The first column of the table is a list of numbers. I tried
converting the table into text with manual line breaks and tab stops to
divide columns and rows, but that didn't solve my problem.

Excel pastes the data into several rows. When I try to merge them, I
get a warning that the selection contains multiple data values, and
merging into one cell keeps the upper-left most data only.

What I tried that didn't work:
* Formatting the Excel cells as text before pasting the data.
* The various options for "Paste Special." The closest I got was
inserting the table as a Document Object, which could be a workaround,
I guess.
What I am saving for when all else fails:
* The obvious solution of copying row by row into one Excel cell.

The data in the table is information about my dad's medications. I
would like to have reference charts of how to identify the strength of
each tablet by its color and markings. I got the info from the
manufacturers' websites and entered it into tables in Word, which I
would like to copy into a more comprehensive file I am creating in
Excel. The first column of each table is the strength of the tablet,
entered as 1 mg., 2 mg., etc. The subsequent columns describe the
shape, color, and markings. There are 3 tables, each with about 4-5
rows.

Is there a way to copy each one - whether as a table or as text - into
a single Excel cell without losing data?

Many thanks.




Hello,

Here is my issue:

I have a list of SKUs in Excel and I need to add one same word to 500 cells that have pre-existing text in the cells. See example below:

1) List of SKU's in cells:

34
35
39
55


2) text that needs to be added IN FRONT of every number:

DF

So the result would be:

DF34
DF35
DF39
etc.

How do I do that in a formula and not manually? I found another similar thread and I understand how to do it from the instructions there but the same text goes on the back and the result is: 34DF but I need it to be like: DF34.

I am using Office 2007 for mac.

Thank you for the help,
Chris


Hi,
I am looking for a method/formula that will reverse multiple text entries from "abcde" to "edcba". The entries are composed of several words that need to be reversed ie from "ab cde fg" to "gf edc ba"

Thanks


I have the code which filters my spreadsheet and copies that dataset but I think I need to add an IF that skips the copy line when the criteria is not met. Any ideas?

Thanks all!!


I have two columns in my spreadsheet. Column A has no blank fields, Column B has some blank fields. I would like to show the text from Column A in the same row of Column B only when Column B is blank.

So, basically I'm after: If column B has text, do nothing. If column B is blank, then list text from Column A.

Right now I've created Column C with this formula =IF(ISBLANK(B1), A1. That's working to get the text from A1 when B1 is blank. What can I do/add to get it pull the text from B1 if there is text there? Or...is there a better approach altogether?


Hi there, I have a string of numbers and I would like to add zeros to the end of it.

For example my numbers are formatted like this: 1234 and I would like to add zeros to the end so it looks like this: 123400 (no decimal).

I would do it manually but I have a column of over 2000 different numbers.

Thanks for your help, you guys are great!


Hello,

As the title goes this is as simple as it gets. The macro worked good when I was using excel 2003 but now that I have 2007 everything works for except the resizing (Picture.Width & Picture. Height).

Code:

Sub AddImage()
Dim strPath As String
Dim strFile As String
Dim Picture As Object
Dim Response As String
Dim C As Range
strPath = "C:\\Photos\"
 
For Each C In Range("A1", Range("A65536").End(xlUp))
 If C 0 Then
 With C.Offset(0, 1)
 Set Picture = Nothing
 On Error Resume Next
 Set Picture = Sheets("Raw Pix").Pictures.Insert(strPath & C.Value & ".jpg")
 Picture.Top = .Top
 Picture.Left = .Left
 Picture.LockAspectRatio = msoTrue
 Picture.Width = 157
 Picture.Height = 138

 
 End With
 End If
 
NextC:
Next C
End Sub


I have tried to record some macros while I am resizing the pictures but to no avail the macros are blank.

Please help

Thanks!


hi,

I would like a formula that would list all the items in row B that match the criteria in row A. the first cell with formula would list the first item, the second cell with the formula would list the next item, and so forth. Also, column B might have a duplicates that should be listed. Is this possible? I cannot manipulate the order of the original items (ie, filters) because this data is being used to derive other formulas.


I have a problem: I can't show some rows after they've been hidden with a macro. I tryed the "select all / unhide", but it doesn't work.

I noticed the row numbers are all blue now. What does it mean? (This could be the answer..:P)


I have a textbox in my userform where anything typed in the box is entered into cell A2 in my worksheet. However, multiple lines of text are not properly displayed in my worksheet. I have enabled MultiLine and EnterKeyBehaviour in the properties window of the textbox so I can type multiple lines in my textbox. The problem is that when I press the button to enter the text from my txtbox to cell A2, the cell doesn't actually display the text in separate lines. It just places a square symbol in the place that enter should have been pressed and the text should have been split into separate lines.

How can I get the separate lines of text in my textbox to be properly displayed in a cell? Thanks for your help in advance..


I have a large database with names, addresses etc. When I try to make changes
to the email address, like change the font color or change on letter, Excel
tries to email the address. If I click in the formula bar to make the
changes, it automatically changes it back to all blue text and underlined. I
have tried to format all of the cells as "Text" to show as entered, but it
doesnt work. PLEASE HELP



I have set up an excel file that helps put together different text components using the vlookup and concatenate formulas for use in one plain text document .

When I try to paste the finished result into notepad/textpad/word it adds extra double quote marks throughout the text. Does anyone know how to paste it without these marks?

Thanks!


Is it possible to import a single text file into Excel, splitting the incoming data across multiple worksheets rather than a single worksheet? Each each row on the text file would be evaluated by the value in one of it's "columns" and written to the appropriate worksheet. The file is "!" delimited and has 11 columns for each row.

Currently, I import the file into one worksheet and cut/paste the rows manually into new worksheets/tabs. The files are very large, sometimes exceeding the 65,536 row limit, which I could avoid if the data was split out coming in.

Any help anyone could provide would be WONDERFUL. Thanks!


I have a problem sometimes. I will click on a cell to add information.
The cell turns blue and then wherever I move, it highlights those to.
No matter where I go on the page. If I Alt-Tab and work in another
program on my computer, that excel page keeps highlighting wherever
I move even in those other programs (I know this sounds confusing).
When I return to excel thousands and thousands of cells are blue.


The biggest problem is that the highlighting won't turn off, no matter
what. I can't select anything from the tool bars, do any work on the sheet or close the program.
I can close it only with the task manager but when I open it again,
the cursor is still stuck in the highlighting mode and won't perform any
other functions.

Do you think this is a problem with my computer, the excel program? I have changed my mouse and this didn't help.
Is there some shortcut to turn off this highlight feature other than restarting
my computer. Which is the only current way I can get rid of it.

Thanks for any advice,


I have created an excel spreadsheet to track attendance of an exercise class I am teaching. I would like to know each person's current percent attendance. (so if we have had 5 classes and they have attended 4 of those classes then it should show 80%). I would like it to keep a running tab, so as I add in the person's attendace it will continue that count, up to the 26 classes that are being offered. I thought if I did a sumif/countif I could get it to work, but it doesn't. Here is what I tried: =sumif($e$4:$ad:4,">0")/countif($e$4:$ad$4,">1"). Do you have a suggestion that would work? Thanks!


Okay, so I tried (for hours) to find a solution to this and have finally gotten frustrated to the point where i think asking here will be my best bet.


Basically I want to export data from excel worksheets to various places on a word document I had. I have created a word template as well as bookmarks for that template, as that seems to be the recommendation for performing such a task with excel.

My problem is ... Now what?

I basically have 1 constant worksheet that contains all my affiliates info (Name, Address etc), which i'd like to use to populate some bookmarks in my word template, and then i have another worksheet for the previous month, indicating sales etc for that affiliate. I intend on adding a new worksheet every month that i would use to create new invoices.

Furthermore, i'd really like to be able to update a new file (or perhaps a new page on the same file) for each company with the use of a button, similar to the one's you'd see in an access form.

Can anyone help? Let me know if you need me to clarify.

Thanks!

Matt


I'm trying to use VBA to go to a website that requires a User Name, Password, and a Submit Button.

So far I can get everything to work besides the Submit part. The code runs without errors, but doesn't actually "hit" the submit button on the webpage.

For posting, I removed my actual user name and password and and used the generic " User Name " and " Password " highlighted in blue.

I highlighted another section in green that I took from a previous post hoping it would solve my problem. The link is: /


Here is the code I am using:

Sub GoToWebSiteAndPlayAroundNew()

Dim appIE As Object ' InternetExplorer.Application
Dim URL As String


Set appIE = CreateObject("InternetExplorer.Application")
URL = " / "


With appIE
.navigate URL
.Visible = True

Do While .busy: DoEvents: Loop
Do While .ReadyState 4: DoEvents: Loop

.document.getelementbyid("fUserName").Value = " UserName "
.document.getelementbyid("fPassword").Value = " Password "

End With


On Error Resume Next
x = 0
For Each mitem In IE.document.all
mitem.Value = "x"
x = x + 1
Next

x = 0
For Each mitem In IE.document.all
If x = "Submit" Then
mitem.Click
Exit For
End If

Next

End Sub


Hi, can anyone help me with the below...

Im looking for a vlookup formula which will give me the maximum number from a list which contains multiple matches, i.e.

Lookup number 1 from column A, then give me the largest number from column B

example table:
A B
-----
1 5
2 2
1 11
3 2
4 5

the result would be 11

thanks all!


I have a merged cell which contains a text with multiple lines.
I want to split the lines into multiple rows.Each line should come in a different row.
I want to do this using macro.Is there is anyway to do this?
There is an option to split the cell into multiple cells on the basis of the delimiter, but there is no option to split them into rows.


Hello,

I need to know how to auto-fill text based on text in another cell. For
example:

Every time I enter "CHS" in Column B, I want Charleston to auto-fill in
Column C.
And when I enter "SAN", in Column B, I want San Diego to auto-fill in Column
C.

How can I set up a list like this? Any ideas?

Thanks!

Jason



I have a spreadsheet with thousands of columns. Due to poor planning on my part I need to insert a column between each existing column (e.g. I need to insert a column between the existing columns A and B, B and C, etc...). I would rather not have to do this manually thousands of times. Is there an easy way to do this?

Thanks in advance.



Related pages


travel comp time worksheetchartlinesvba file copyvba dcountexcel payroll calculatoroutlook record macroexcel overlapping bar chartoffice.interop.exceltracking stocks with excelxirriscalculation of gratuity with examplemultiple criteria vlookupsumifs with multiple criteriaexcel vba messagemerging cells in excel 2007footy tipping spreadsheetexcel formula max lengthmulti vlookupvba for loop decrementpopulate in excelexcel conditional formatting greater than or equal tofix a cell in excelvb color indexconvert outlook calendar to wordvba if elseifleague secretary.comconvert to julian date in excelhow to convert dat to excelexcel vba joinquotations format in excelloan amortization excel formulapayroll decimal conversion chartexcel vba msgbox vbyesnocreating a golf handicapvba code to connect to sql serveropen mde filepowerpoint error accessing fileexcel vba insert columnstdole2.tlb downloadexcel iserror formulaapply formula to entire column in excel5260 avery templatevba screen updatehow to insert automatic numbering in excelexcel autocorrectgrid ref converterconvert kb to gb calculatordownward arrow symbolvba error 9how to unhide rowsexcel vba cells offsethow to calculate mean and standard deviation on excelexcel row height in incheshire purchase maths formulaarray macro excelexcel math symbolsvba upper casecalculate percentage in excel sheethow to create a stacked bar graph in excelvba requerypreceding zeros in excelexcel vba close formsla calculation formulaexcel vlookup 2 criteriademand curve in excelexcel tangent lineexcel formula percent changeexcel vba paste specialvba export report to excelquartile graphconcatenate columns excelspecial symbols in excelsplit window in excel