Formulas
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Formulas

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Mr Excel, as you can see by my e-mail address I am in the military. I have been working on a spreadsheet that will compute a variety of calculations for the accounting of personnel and their individual data. Iam at a loss as to how to calculate the ranges I want. I can copy the correct formula to the rest of this project once I get the right answer. I have talked to many personnel in the system who are "experts" and continue to get that "deer in the headlight" look from them. I hope you can help. Here is my problem: In a any cell or perhaps another worksheet, I need to search two ranges for certain criteria and get a total number. I want to search i.e. B4:B40 for any cell that contains "O*" (the letter O and 1-9 using the wildcard symbol). I then need that same formula to search i.e. K4:K40 for any cell that contains "P" (the letter P). I then need the sum of how many cells met both sets of criteria. I greatly appreciate any help I can get. This will assist hundreds of troops.

    SFC Michael F. Lingeman

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,790
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-18 13:47, lingemanm wrote:
    Mr Excel, as you can see by my e-mail address I am in the military. I have been working on a spreadsheet that will compute a variety of calculations for the accounting of personnel and their individual data. Iam at a loss as to how to calculate the ranges I want. I can copy the correct formula to the rest of this project once I get the right answer. I have talked to many personnel in the system who are "experts" and continue to get that "deer in the headlight" look from them. I hope you can help. Here is my problem: In a any cell or perhaps another worksheet, I need to search two ranges for certain criteria and get a total number. I want to search i.e. B4:B40 for any cell that contains "O*" (the letter O and 1-9 using the wildcard symbol). I then need that same formula to search i.e. K4:K40 for any cell that contains "P" (the letter P). I then need the sum of how many cells met both sets of criteria. I greatly appreciate any help I can get. This will assist hundreds of troops.

    SFC Michael F. Lingeman
    I assumed the first criterion to be strings like O1,O2,...,O9.

    =SUMPRODUCT((ISNUMBER(SEARCH("O"&{1,2,3,4,5,6,7,8,9},B4:B40)))*(ISNUMBER(SEARCH("P",K4:K40))))

    Aladin

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-18 13:47, lingemanm wrote:
    Mr Excel, as you can see by my e-mail address I am in the military. I have been working on a spreadsheet that will compute a variety of calculations for the accounting of personnel and their individual data. Iam at a loss as to how to calculate the ranges I want. I can copy the correct formula to the rest of this project once I get the right answer. I have talked to many personnel in the system who are "experts" and continue to get that "deer in the headlight" look from them. I hope you can help. Here is my problem: In a any cell or perhaps another worksheet, I need to search two ranges for certain criteria and get a total number. I want to search i.e. B4:B40 for any cell that contains "O*" (the letter O and 1-9 using the wildcard symbol). I then need that same formula to search i.e. K4:K40 for any cell that contains "P" (the letter P). I then need the sum of how many cells met both sets of criteria. I greatly appreciate any help I can get. This will assist hundreds of troops.

    SFC Michael F. Lingeman
    =SUMPRODUCT((B4:B40="O*")*(K4:K40="P"))

    I hope that helps


  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Go with Aladin, he read your question better, I missed some stuff he caught.

    I did not solve the wildcard problem...



    [ This Message was edited by: John McGraw on 2002-04-18 14:13 ]

    [ This Message was edited by: John McGraw on 2002-04-18 14:15 ]

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    {=SUM((B4:B40="O"&{1,2,3,4,5,6,7,8,9})*(K4:K40<>SUBSTITUTE(K4:K40,"P","")))}

    Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. For more on array formulas see the Excel Help Index Topic for "About array formulas and how to enter them".

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hi

    not disrespect to anyone but CSe can trash wkbks and unless you Ok with these i say go with Aladins Sumproduct, i have late had WkBks with buillions of arrays and they distroy good work if over used.

    NOT picking or dissing any posts just as i have seen and been involved in as late MY opinion not a solution...

    I recomend sumprduat as much as poss,


    Suggest if ARRAYs are you god, Mark W is the man and check him, i scratch my head most days and think arrays ??? eh?? how does mark solve these, and solve he does...


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-18 14:42, Jack in the UK wrote:
    hi

    not disrespect to anyone but CSe can trash wkbks and unless you Ok with these i say go with Aladins Sumproduct, i have late had WkBks with buillions of arrays and they distroy good work if over used.
    People are killed by automobiles every day. Let's stop using those too! Or... perhaps we could learn how to operate them intelligently?

  8. #8

    Join Date
    Mar 2002
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-04-19 06:51, Mark W. wrote:
    People are killed by automobiles every day. Let's stop using those too! Or... perhaps we could learn how to operate them intelligently?


    "Or... perhaps we could learn how to operate them intelligently?"

    Not much chance of that! Better stop using them!


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com