Wildcards with Array Formulae

Hi, 
I'm looking for some help with a formula I'm using to pull statistic
out of a client spreadsheet. The formula uses the SUMIF command to loo
at one column, check for a text value then look at another colum
within the same row and check for another text value. It returns 
value of one if both are true and a value of 0 if one or none are tru
then keeps a total. It's a pretty standard array formula, but I need i
to use wildcards because the second column can contain value A, value 
or value AB. I want the formula to pick up all instances where value 
appears, including where it appears within AB. The formula I have use
is as follows - 

=SUM(IF((A1:A1000="string1")*(F1:F1000="A"),1,0))

This works to an extent, but doesn't pick up instances where value 
appears in a cell containing AB as it is looking for only A. If I add 
wildcard "*A*" the formula doesn't work any more... Does anybody hav
any suggestions? Any help would be very much appreciated...!!! Thank
in advance.

Jame

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

0
11/18/2003 11:38:54 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
427 Views

Similar Articles

[PageSpeed] 6

"Jamesmoore" <Jamesmoore.x384n@excelforum-nospam.com> wrote in message
news:Jamesmoore.x384n@excelforum-nospam.com...
>
> Hi,
> I'm looking for some help with a formula I'm using to pull statistics
> out of a client spreadsheet. The formula uses the SUMIF command to look
> at one column, check for a text value then look at another column
> within the same row and check for another text value. It returns a
> value of one if both are true and a value of 0 if one or none are true
> then keeps a total. It's a pretty standard array formula, but I need it
> to use wildcards because the second column can contain value A, value B
> or value AB. I want the formula to pick up all instances where value A
> appears, including where it appears within AB. The formula I have used
> is as follows -
>
> =SUM(IF((A1:A1000="string1")*(F1:F1000="A"),1,0))
>
> This works to an extent, but doesn't pick up instances where value A
> appears in a cell containing AB as it is looking for only A. If I add a
> wildcard "*A*" the formula doesn't work any more... Does anybody have
> any suggestions? Any help would be very much appreciated...!!! Thanks
> in advance.
>
> James

Choose from these possible array formulas:
=SUM(IF((A1:A1000="string1")*((F1:F1000="A")+(F1:F1000="AB")),1,0))
=SUM(IF((A1:A1000="string1")*(LEFT(F1:F1000,1)="A"),1,0))
=SUM(IF((A1:A1000="string1")*ISNUMBER(FIND("A",F1:F1000)),1,0))
The first finds, specifically, "A" or "AB". The second finds "A" as the
leftmost character (like "A*"). The third finds "A" anywhere (like "*A*").

Note that, as an alternative, you could use one of the equivalent SUMPRODUCT
formulas that don't need to be array-entered:
=SUMPRODUCT((A1:A1000="string1")*((F1:F1000="A")+(F1:F1000="AB")))
=SUMPRODUCT((A1:A1000="string1")*(LEFT(F1:F1000,1)="A"))
=SUMPRODUCT((A1:A1000="string1")*ISNUMBER(FIND("A",F1:F1000)))


0
Paul
11/18/2003 12:10:54 PM
Why doesn't

=SUMIF(A1:A1000,"*A*",F1:F1000)

work? Works for me..

-- 

Regards,

Peo Sjoblom


"Jamesmoore" <Jamesmoore.x384n@excelforum-nospam.com> wrote in message
news:Jamesmoore.x384n@excelforum-nospam.com...
>
> Hi,
> I'm looking for some help with a formula I'm using to pull statistics
> out of a client spreadsheet. The formula uses the SUMIF command to look
> at one column, check for a text value then look at another column
> within the same row and check for another text value. It returns a
> value of one if both are true and a value of 0 if one or none are true
> then keeps a total. It's a pretty standard array formula, but I need it
> to use wildcards because the second column can contain value A, value B
> or value AB. I want the formula to pick up all instances where value A
> appears, including where it appears within AB. The formula I have used
> is as follows -
>
> =SUM(IF((A1:A1000="string1")*(F1:F1000="A"),1,0))
>
> This works to an extent, but doesn't pick up instances where value A
> appears in a cell containing AB as it is looking for only A. If I add a
> wildcard "*A*" the formula doesn't work any more... Does anybody have
> any suggestions? Any help would be very much appreciated...!!! Thanks
> in advance.
>
> James
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>


0
terre08 (1112)
11/18/2003 12:13:12 PM
Reply:

Similar Artilces:

Struggling with formula
Hi I have a spreadsheet withthe following data: 28/08/2008 09/05/2008 HINGE SA28553 8 =A31.10 =A30.77 =A36.16 28/08/2008 09/05/2008 HINGE SA28566 8 =A31.10 =A30.77 =A36.16 20/08/2008 28/08/2008 HINGE SA28389 24 =A31.10 =A30.77 =A318.48 14/08/2008 21/08/2008 HINGE SA28268 12 =A30.00 =A30.00 What I need to see is the total number of units, of which XXX are Free of Charge between xxxx date Sorry, but I am struggling and got so far =3DCOUNTIF(J2:J924,"=A30.00") but struggling with the rest, can anyone help Ta BabyPink, In general: =SUMPRODUCT((FirstDate<=Date"))...

COUNTA, but not formulas returning empty string
(Using Excel2000) I have a column (A) where I have entered a formula =IF(FromAccessTotalSubs!A3="";"";FromAccessTotalSubs!A3) (the row number increasing on each row). The idea is to copy the column from another sheet, and ensure that an empty string is displayed rather than 0 if the copied cell was blank. When I try to use =COUNTA(A:A) in another cell to find out how many cell in the column that contains some information (not empty string..) I finf that COUNTA return the number of cells that contains the (first) formula, rather than how many cells displays a value to the ...

Trouble with copy and past of formulas in Excel
I got a sheet from one of my collegaes that was causing problems, it sometimes looses formulas (It looks like a problem with connections through our network) and you get true untrue name errors, after refreshing the cell the problem is gone. Now I'm having trouble with copying formulas, after trying some copy and paste it crashes with dr. Watson. It seems that there is some timeout problem. More people have trouble with this with the somewhat larger sheets. ...

Formulas inside Pivot Tqables
I just upgraded to Excel 2003 and I can no longer do formulas in pivot tables (in Excel 2000, Right-click inside a pivot table, the 'formulas', then 'calculated field' or 'calculated item'). There is no "formulas' opein in Excel 2003. Any ideas? ...

Changing cell from formula to data.
Is there a way to change a cell from a formula (that is returning data) to the data it is returning. I realy hop it it becuse it would help out a ton. -- Thanks TimM If you select the cell and copy then edit - pastes special - values on the cell you will have the value and not the formula. HTH -- Sincerely, Michael Colvin "TimM" wrote: > Is there a way to change a cell from a formula (that is returning data) to > the data it is returning. I realy hop it it becuse it would help out a ton. > > > -- > Thanks > TimM Hi, Select the cell(s) contain...

Excel formulas 01-06-10
I have a worksheet that we would like to use to calculate price + labor for work. The price formula has me stumped. I would like to be able to put in the price in one box and have the formula determine the amount at which to multiply it by. For example: Price = 20.00 then the formula would determine how many to multiply it by (3.25) from the range given below. Range is 0-2.00 *4 2.01-5 *3.75 5.01-10 *3.5 10.01-20.00 *3.25 20.01-40 *3 40.01-60 *2.75 60.01-100 *2.5 100.01-150 *2.25 >150.01 *2 I have the labor formula and then the total is a sum formula. Please help...

Countif formula
Hi, I'm new here and I hope I'm posting in the right forum. I require help with a formula that has left me scratching my head for a couple of hours. I have a column of data cells that lists 3 different options, for example: option1 = Good option2 = Ok option3 = Bad i need a formula that will count how many 'Good' entries there are and to work it out as a percentage of the total number of entries (all 3 options). I hope that makes sense. If more info is required, please let me know. Thanks for your time -- godonlyknows --------------------------------------------------...

Help with Lookup/Sum Formula
I have a spreedsheet with this type of data, for example Product Group Quantity Size 2812 10 0.75 2812 100 0.75 2812 300 0.5 2817 100 0.25 2817 200 1 2845 1000 1.5 I want to be able to set up a formula that you enter a product grou (2812) in a cell and it will for that product group go and sum up al the quantities for each size. The formula would go in the Sum colum For Example ...

Need a formula that tags one table based 2 columns in each table
I have two tables on one worksheet, Table A and Table B. Each table contains two column with X and Y coordinates, all values are numerical. The coordinate system is irrelevant. I need to "tag" all XY coordinates in Table B that have a matching XY coordinate in Table A. If Table A coordinates are in columns A and B, and Table B coordinates are in columns C and D, then I want to place the text "hit" in column E next to each coordinate pair from columns C and D that match a coordinate pair in columns A and B. The ranges for the respective tables have been named ACOORD and...

Formulas to highlight cell if condition is met
i wonder if there is any way i can highlight a cell if a condition is met? eg i want to highlight the cell if the num in the cell is greater than 1. do i hv tio use macros? -- hmmm ------------------------------------------------------------------------ hmmm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25836 View this thread: http://www.excelforum.com/showthread.php?threadid=481784 No you can use conditional formatting Select the cell Menu Format>Conditional Formatting Set to greater than Set value to 1 Click the Format button Select the Pattern Tab ...

Formula to display Count of Days in given month, using list of Start and End Dates
I have a large number of rows that contain a start date and end date. I am attempting to generate a count of days within that date range that are in a particular month, for example, Jan of 2011. Any suggestions for a formula that could be used to display the amounts shown in Column C would be helpful. Layout is: A B C Start Date End Date Count of Days in Jan2011 1/10/11 4/7/11 21 2/6/11 3/1/11 0 12/20/10 2/15/11 31 Etc. Dave, I think the best way is to create a table f...

Help With Formula #5
I have data in a worksheet A B C 1 Verify 2% 99% 2 Audit 2% 98% 3 Account 5% 97% 4 Forward 4% 96% 5 Place 5% 95% I have another cell (F1) in the sheet that i would like to list the text in A1, but only if B1 is at least 5% if not then I want it to list A2, but only if B2 is at least 5% and so on down to A5. Please Help. What happens with row 3 and row 5? -- Don Guillett SalesAid Software donaldb@281.com "Report Lackey" <anonymous@discussions.microsoft.com> wrote in message news:033b01c3d62d$ebd800e0$a401280a@phx....

Excel formula needed or how to
I have got a rota with E for Early, L for Late and N for Night. For one particular date, e.g. in C3 there is the date, and C4 to C8 ar the various E , L and N. Now on the other side I want see who is actually working E, L or N at quick glance and requires a formula to convert the E to the nam allocated to that person and can it be done in such a way to omit th person who is not doing the E from the column of names and avoid an blank rows. Attached is a brief example but there is about 35 staff on that. 4/7/04 5/7/04 6/7/04 John EL EL EL Paul L O L Charlie N N O Jennife O L N Charlene EL ...

How do I set up a weighted formula in Excel?
Can someone help me please? I need to do a weighted formula in Excel 2007, where the total of the numbers equals 90% of one number and 10% of another number. Is it possible to do this? On Mon, 10 May 2010 15:53:01 -0700, ib1qb <ib1qb@discussions.microsoft.com> wrote: >Can someone help me please? I need to do a weighted formula in Excel 2007, >where the total of the numbers equals 90% of one number and 10% of another >number. Is it possible to do this? > =sumproduct(numbers, weights) e.g. =sumproduct(a1:a2,{0.9,0.1}) --ron Sa g1 to j7 conta...

Excel formula calculating between #'s
I am working on a bonus chart and this is what I am trying to do: If an employee keeps his register balances between -$10.00 thr +$10.00, they recieve 100 points. If they are out of that range the get 0 points. I have tried playing with "IF" formulas until I am blu in the face. Please help Thanks Mike Manic -- Message posted from http://www.ExcelForum.com Hi Mike try something like =IF(AND(A1>=-10,A1<=10),100,0) -- Regards Frank Kabel Frankfurt, Germany "manickmj >" <<manickmj.15ftz3@excelforum-nospam.com> schrieb im Newsbeitrag news:manickmj.1...

Save a date as a variable and run/convert formula against the vari
Good Day, What I'm needing to do is retrieve a date field from a closed workbook, save it as a variable and then calculate a week number based on it. which I'll use for another function. I've attached two types of code I'm struggling with in VB and hope someone can help... The first part retrieves a date from a close workbook and I need to change "MsgBox" to a varible to save the date retrieved. Sub GetVal() MsgBox TheValue("C:\GssReports", "gssreport MTTR.xlsx", "gssreport 1 ", "K6") End Sub Function...

help with formulas
I have a list of applications that have gone overdue. Here is a sample of my list: Program Code Case # Delay Code P60 401133012 P60 721283900 AG P30 821362112 P30 828190110 P60 950351513 CI L60 217075010 CI S60 810474413 OA Q30 235391903 OA S60 167004803 OA S30 893474708 OA S30 676254115 CI S60 712233808 There are several different program codes for the applications, and a few different delay codes, leading to several differe...

Translate excel formulae
Does anyone know if there exists a 'document' showing translation of excel formulae from English to French (e.g. =counta is =NBVAL)? -- Helen (English, but living in France) KeepItCool has a translator utility at: http://members.chello.nl/keepitcool/download.html Helen wrote: > > Does anyone know if there exists a 'document' showing translation of excel > formulae from English to French (e.g. =counta is =NBVAL)? > -- > Helen > (English, but living in France) -- Dave Peterson Thanks Dave, although I can't open their download without deactivating th...

Word wrap macro deletes formula
I created a macro to wrap text on a protected worksheet in which each cell contains a formula importing text from another worksheet. The cells are locked and formulas hidden but when I run the macro, it wraps the text and deletes the formula> how can I stop it doing this? The macro is as follows: Sub Wordwrap() ' ' Wordwrap Macro ' Macro recorded 01/10/2004 ' ' Keyboard Shortcut: Ctrl+w ' With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False ...

Help with creating a formula
Hello! I am trying to create a formula that will sum up certain criteria from one column as it relates to criteria in the same row in another column. For example, I want to sum up all the entries that begin with the letter "H" in column A as it relates to an entry of "2" from column B that is in the same row as the "H" entry from column A. COLUMN A COLUMN B H10 2 G01 2 H09 3 H15 2 H12 2 If there is such a formula...

Excel does not calculate my formulas
This is very strange...Excel does not calculate any formula. I can do a simple =A1+A2, and it just return a zero value. I made sure of the following: - Cell is set to General (tried it with different cell settings too) - Autocalc is on in settings (even a manual F9 calc doesn't work) -- Martin Verville We might need a few more clues: What is in A1? What is in A2? -- David Biddulph "Martin" <Martin@discussions.microsoft.com> wrote in message news:A9A25385-5BE8-4D38-A7A7-15E523C69DD9@microsoft.com... > This is very strange...Excel does not calculat...

FORMULAS #35
WHERE CAN I GET A LIST OF ALL FORMULAS MICROSOFT EXCEL USES? Excel's help for worksheet functions? Take a look at Peter Nonely's workbook that describes lots of functions: http://homepage.ntlworld.com/noneley/ If Peter's site isn't working, but Ron deBruin has a copy at: http://www.rondebruin.nl/files/xlfdic01.zip Norman Harker has his version at Debra Dalgleish's site: http://www.contextures.com/functions.html BLUPHISH wrote: > > WHERE CAN I GET A LIST OF ALL FORMULAS MICROSOFT EXCEL USES? -- Dave Peterson ...

how to declare an array of CString?
Hi, To have a CString array, which one of the following two is a correct declation? I was told the 2nd one is correct. Does it mean that CArray internally uses a pointer instead of reference for its parameter lists whenever a CString is in consideration? CArray<CString, CString&> a; CArray<CString, LPCSTR> a; Thanks for your help. Why not just use CStringArray? KS "May Young" <tony@srac.com> wrote in message news:409AD185.8010906@srac.com... > Hi, > > To have a CString array, which one of the following two is a correct > declation? I was...

Formula #16
I need to have a formula that can add a row of cells with a variable number of colmns. Columns will be added and deleted everyday, so the formula needs to have a variable range. I hope this is clear. Thanks, Bernie ...

polynomial formula for a line graph...
Hi. I haven't looked at this feature in a couple of years. Excel can give you the formula for a charted function... what I mean is, given a few data points, it can create a best fit curve, and display the function that it used to do that... Would someone remind me where, please? Thanks. Hi, Right click the data series and pick Add Trendline. From the dialog you can choose the type of trend line and whether to display the formula. Cheers Andy mark wrote: > Hi. > > I haven't looked at this feature in a couple of years. > > Excel can give you the formula for ...