# Wildcard Characters Okay In Countdiff And Sumproduct?

Feb 2, 2010Wildcard characters okay in countdiff and sumproduct? .......

View 9 RepliesWildcard characters okay in countdiff and sumproduct? .......

View 9 RepliesADVERTISEMENT

Tring to get the following spread sheet to calc column C using two criteria

Criteria 1 Column D must contain HC Investor anywhere

Criteria 2 Column A must be less than Reimbrusement - 11

The following formula in G2 appears to have an error in criteria 1 ....

I am having great difficulties in finishing this formula off.

It is a sumproduct containg many criterias, however one of them is to only add up a certain coloumn if another column begins with L&L...

The coloumd contains L&L and a number, but the number changes so i need the number to be a wildcard. I have tried ="L&L???" but this does not work.

=-SUMPRODUCT((Catagory="STK Retail")*(MONTH(Inv_Date)=3)*(YEAR(Inv_Date)=2008)*(How"Account")*(Order="L&L???")*(Stock))

I have spent the last 2 1/2 hours trying to figure this one out on my own. I have attempted multiple possiblities from the forum, however without success.

Okay, I need a count based on 2 criteria. My data source is a different workbook.

My last attempt on a formula is:

=SUMPRODUCT(('[eTE Status - TX HSP Math 2009.xls]Project'!$B$2:$B$248=D2)*('[eTE Status - TX HSP Math 2009.xls]Project'!$A$2:$A$248="978*"))

Column A - ISBN #

This will ALWAYS begin with 978 or will contain N/A. There are no blank cells in this column

Column B - Grade

In the formula above, cell D2 represents the Grade criteria. And in the second part of the criteria I was trying to create a wildcard so all numbers beginning with 978. Excel "accepts" this formula with giving me an error message, but yields 0 as a result rather than 34.

I'm trying to create 2 separate data sets based on year (06 vs. 07). To do so, I created a column and created an if then statement that populates with Yes If the date value is "like" 2006". Excel didn't like the ? or asterik. Is there some other syntax that will accomplish.

While I open to other approaches for filtering these 2 data sets, Ive been wondering for awhile about excel and wildcard logic.

I am attempting to set a value in a cell based on the content of another two cells. I want it set the value to 35 or 38 only if the txtProgram.Text contains Math*. Unfortunately, it always changes it to the Math value based on txtGrade.Text. What mistake am I making?

Application.Goto Reference:="SEFirstPass"

If txtGrade.Text Like "K" Or txtGrade.Text Like "1" Or txtGrade.Text Like "2" Then

ActiveCell = "27"

ElseIf txtGrade.Text Like "3" Or txtGrade.Text Like "4" Or txtGrade.Text Like "5" Or txtGrade.Text Like "6" Then

ActiveCell = "30"

End If....................

Here is what I have so far:

=SUMPRODUCT(--(ISNUMBER(SEARCH("Red",A9:A56))),B9:B56,L9:L56)/SUMPRODUCT(--(ISNUMBER(SEARCH("Red",A9:A56))),B9:B56)

So in column A is the text string i.e Big Red Chiquita Apple

Column B is the dollar value

Column C is another variable.

If I want to find "Red" and "Apple" ONLY within same cell. What would be my best bet?

Sumproduct formula with selection criteria of "A", "B"... in the first column and numeric values in the next colum. The selection is controled by a List where the user can choose "A", "B", ... ,or "ALL". What wildcard-type (pseudo) is needed to select all values when "ALL" is chosen?

I'm using Sumproduct because there is other selection criteria, but it should not impact this part of the formula.

Example: Sumproduct((A1:A100=X1)*(B1:B100)) , where A=selection aray, B=numeric value, X1=corresponding list selection to A

I am having problem filtering out criteria by putting together a sumproduct formula. I would want to use wildcard to populate 2 criteria from the data sheet, they are "Ducted Evaporative Cooler Single Stage 1 Tier" and "Ducted Evaporative Cooler Single Stage X-1 Tier". In my formula, I have used a wildcard "Ducted Evaporative * 1 Tier*" for the prior and "Ducted Evaporative * X-1 Tier*" for the later. I would like to know whether by checking my formula that i am using....

View 9 Replies View Relatedget the name of the File in a Vlookup VBA using wildcard characters. I have to do a VLOOKUP from a specific file which is stored in the same folder as the file in which I need to get this VLOOKUP done, however the name can change anytime keep a word common only for this specific file...

Ex:

I have a File called "Mapping as on 8th May" from where i need to lookup some information for my file and I intend to do this using VLOOKUP in VBA in a Module but since the name can change to anything such as"Mapping as on 16th May" I cant use it in the VBA code, so is there a way around this where If I have this file stored in the same folder then I keep the word "Mapping" specific and unique for all other files in the same folder that the search can pull this file name to be further used in my lookup..

how to use the wildcards in VLOOKUP lookup range for the file name as the columns are going to remain the same...

I have a worksheet report that counts question answers filtered by facility, date range and case manager. I use Data Validation "List" criteria to make the in-cell drop-down lists for the criteria of the variable values. For most of them the selected value works fine, but for the case manager drop-down I need a value in the list, other than the peoples names, to represent "All" or be a wildcard and include all values in the selection. Here is the formula:

View 4 Replies View RelatedI have a sheet with Names in it. I need to look those names up and find which org they belong to. The problem is the the names have many characters after them, and many names belong to a few orgs, with a the rest belonging to many orgs. (See below)

I have been trying to get a Vlookup to work, but since it can't go left, it won't look up the orgs that don't own at least three names. And I can't get the search to work on the names.

Names:

WSGHG1-8410YFH

WSSMXG-8401B8T

NBSMXG-0310V6R

NBXG-0310V6V

[code]....

I'm trying to write this but it returns a 0 when I know there are 3 records that match this criteria: =SUMPRODUCT(('Invoice-Detail'!J2:J50="NewJob_Post.NET")*('Invoice-Detail'!H2:H50="KY_*")). I think the problem is in the wildcard character. I don't know if I should be using COUNTIF or SUMPRODUCT or something else?

View 3 Replies View RelatedThere might be an easy solution for this, but I can't seem to figure it out. I'm trying to do a vlookup with the lookup value being #CFDMSMCSA*4. But the lookup table also has a value #CFDMSMCSAM4 in it.

It keeps returning the value for this #CFDMSMCSAM4 because of the * in the first string. Any ideas.

I am looking for a bit of assistance with the following formula. What I am trying to do is use sumproduct to sum on 2 variables. I have been trying to make this happen all day and if there is already a post on here to make it work I can't find it.

The problem revolves around the middle section. I am looking to match items in a text string (it is a mixture of text and number but are all as text)

I am looking to match a product that beings 200 (which might be 2000123) I don't want to match something like G200TRF I have tried every combination of left, right, find and search but I still have the same issue.

SUMPRODUCT(--($A$1:$A$1000=($O56)),--ISNUMBER(FIND(200,$E$1:$E$1000)),H$1:H$1000)

Is there an Excel formula to remove the spacebar + characters in red, as shown below? I need to be left with only the last name, first name and the semicolon.

Mouse, Mickey ;

Microsoft Outlook has changed the way that email addresses from the global addressbook copy and paste (from version 2003 to version 2010).

I'm trying to use conditional formatting to highlight cells in a column that have less than 8 characters.

I know the LAN function, but I don't know how to make it work for the conditional formatting.

Have a cell that says something like "123 main street".

I want the cell next to it to = Main if it detects main in the other cell and if not then it should say no. I tried this.

=if(B2="*main","Main","No"

However, it is not working.

I transferring data from the mainframe and some data has ????. I can not replace them with something else because excel think it is wildcard. The reason for this request is I was trying to count the number of cells that has ???? and the count was something different each time. If you want to have fun try it out. It will count ?, ??, ??? but not ????.However my question would be how to change this (????) to some thing like unknown...

For Each rngC In rngJbn

rngC = Trim(rngC)

If rngC.Value <> newvalue And rngC.Value <> "ab-" Then

'MsgBox "****" & rngC & "***"

Résultat = Application.CountIf(Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible), rngC)

MsgBox rngC & " " & Résultat & " " & i

End If

newvalue = rngC.Value

Next

Is it possible to use the wildcard in an IF formula? For instance, I have a spreadsheet with text and numbers in the cells, the number of characters varies in the cells so a left, right or mid is out. I want the formula to look to the cell and if any part of the text contains "ABC" then I want to return 1. =IF(C18="*ABC*",1,"") Cell C18 containts the letters ABC in order but my formula is still returning blanks?

View 4 Replies View RelatedThe idea

[Code] .....

Dropdown list

-------------

O11 = "Land" , "Sea"

P11 = "A - H" , "I - O" , P - Z"

Directory = C:MammalsLandA - HDate_120313 - Carnivore - Code_A3622

The list of filenames in the "A-H" folder has different dates and codes, how do I go about fixing wildcard *Carnivore* so that regardless of the date and code, there will be a match.

list of files in A-H folder

------------------------

Date_120313 - Carnivore - Code_A3622

Date_030213 - Herbivore - Code_A2231

Date_051212 - Hybrid - Code_A4431

How to use this wildcard in a IF statement.

[Code] ......

I have a column I'm adding a value to another cell if a piece of text is found in the column I'm looking in. The * is not working at all.

The use of * as a wildcard in excel can be extremely useful, however, can it be turned off? I work in the education sector and am currently number crunching GCSE results. When it comes to calculating A* grades versus A grades, B grades, etc, I cannot seem to do it because of the wildcard.

I have tried everything I can think of to get this to work.

I am searching column B for anything containing "Tease", then if found, look to see if column J contains a "W".

Excel doesn't report an error, but this doesn't work:

=SUMPRODUCT((B3:B40="*Tease*")*(J3:J40="W"))

Hi I have this formula:

View 3 Replies View RelatedAs far as I know, a wildcard can't be used in an "if" statement. I have a list of "job names" that vary slightly from time to time. When we use a Bunnings "(building account) people give a job name such as "121 William Drive". Sometimes however, the person in the store spells it incorrectly. Is there a way to find a wildcard in the following manner?

Find any word that begins with "121" and ends in "ve"

In a1 it says: "121 wilaim jnes drive" I have tried the following formula in b1:

=IF(a1="121*ve","yes","no")

Why does it say "no"? Is there a formula for using wildcards in an "if" statement?

Trying to count how many cells in column b contain a value.

using

Application.WorksheetFunction.CountIf(Range("B:B"), "" * "")

getting a type mismatch error.

I was wondering is there away where i can lookup A:A anything begging with Ves* ? and then i need to copy the formula down to pick up the next one in the list

im guessing something like =VLOOKUP(A:A,"Ves*") but i havent got a clue

In a recent thread, I learnt how to import HTML, using ADO into Excel.

To import the relevant variables, my code is as below: ...

I need to lookup data from four different columns to match a value in another sheet. The lookup value has to start with "4" in order for it to be the correct match. I've tried the following formula for just two columns:

=IF(VLOOKUP($C2,'Sheet 1'!$D$2:$H$10000,5,FALSE)="4*",VLOOKUP($C2,'Sheet 1'!$D$2:$H$10000,5,FALSE),IF(VLOOKUP($C2,'Sheet 2'!$E$2:$G$10000,3,FALSE)="4*", VLOOKUP($C2,'Sheet 2'!$E$2:$G$10000,3,FALSE),"False"))

The problem I have is that the wildcard function isn't working in my If statement (apparentley that's how Excel was designed). The first vlookup returns the value "40042565" but Excel doesn't show it matching the criteria with the wildcard ("4*").

Any way to lookup the value in C2, search through multiple columns, and return the value that starts with "4"?

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