Formulas

[lingemanm]

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

 

[Aladin Akyurek]

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

 

[John McGraw]

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

 

[John McGraw]

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

 

[Mark W.]

{=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".

 

[Jack in the UK]

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...

 

[Mark W.]

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?

 

[Adcb]

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!