| Home |
| Search |
| Today's Posts |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
test if a logical statement and use the answer within an if st
Thank your Rick, that appears to work just fine.
"Rick Rothstein" wrote: That is a completely different problem than you first posted and, of course, it requires a completely different solution. See if this does what you want... =SUMPRODUCT((ISNUMBER(SEARCH("mech",A1:A1000))*((B 1:B1000=1)+(B1:B1000="A")))) Change the range limits as needed. You can specify a larger range than your current data set if you will adding new data in the future, but note that the row limits for each range in the above formula must be the same (that is, start at the same row number and end with the same row number). -- Rick (MVP - Excel) "BG Mark" wrote in message ... Firstly thanks to all who replied, but I have an additional problem. Column A has the data that I am searching for, and the SUMIF would work ideally if all the corresponding values in column be were of the same format, however column B will contain either 1, A, D or blank and I need also to add 1 to the total for every occurence of value A. eg Column A Column B Mech Shift 1 1 Inst Shift 1 1 Elec Shift 1 1 Mech Shift 2 A Mech Shift 3 D I need a formula that will serch for every occurence of Mech in column A and count only the 1's and A's in column B, so in the above case the total should be 2. In case anyone is wondering, A and D stand for arrival and departure. "Rick Rothstein" wrote: My previous post gave you the method to SUMIF for an exact match... if you need to do a partial match, then just use the asterisk wildcard on either side of the text you are searching for... =SUMIF(A1:A1000,"*partial match me*",B1:B1000) Note the asterisks on either end of the to-be-searched-for string... the above will sum up the values in Column B for any cell in Column A that contains the text "partial match me" either in whole or within a larger text string containing that text. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... If I understand what you are trying to do, I think the SUMIF function is what you will want to use. Assuming Column A has your strings to be searched and Column B has your numbers to be summed (just change the ranges as required; you can specify a larger range than your current data set if you will adding new data in the future)... =SUMIF(A1:A1000,"Some string value",B1:B1000) -- Rick (MVP - Excel) "BG Mark" wrote in message ... How can I use the Find function within an array formula to check a column to locate evey occurence of a string and subsequently sum the corresponding numbers in a different column . . |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF Logical Test | New Users to Excel | |||
| If Statement with Two Different Logical Test | Excel Worksheet Functions | |||
| Logical test in IF statement returning incorrect result | Excel Worksheet Functions | |||
| If statement where the logical test is a range that equals a word | Excel Worksheet Functions | |||
| Logical test | Excel Discussion (Misc queries) | |||
Threaded Mode
