formula

[ageeb]

Hi
My database consist of 3 columns as follow

A B C

Male 55-64 M65-M55-M45
Male 65 M55-M45

I would like a formula that calculated the number of males with B=65 and C= M65. please do help me, I did try
=SUMPRODUCT(($B$2:$B$222="Male")*($C$2:$C$222="55-64")*($D$2:$D$222="*M65*")), but it didn't work.

Thanks in advance

[Asala42]

Hey,

Male 55-64 M65-M55-M45
Male 65 M55-M45

I would like a formula that calculated the number of males with B=65 and C= M65. please do help me, I did try
=SUMPRODUCT(($B$2:$B$222="Male")*($C$2:$C$222="55-64")*($D$2:$D$222="*M65*")), but it didn't work.

Looks like the right kind of formula for the job, but if you're looking for C=65 shouldn't the formula be:

=SUMPRODUCT(($B$2:$B$222="Male")*($C$2:$C$222=65)*($D$2:$D$222="*M65*"))

Adam

[ This Message was edited by: Asala42 on 2002-05-22 07:36 ]

[ageeb]

On 2002-05-22 07:22, Asala42 wrote:
Hey,
I did try this and it still didn't work, any other suggestions

Looks like the right kind of formula for the job, but if you're looking for B=65 shouldn't the formula be:

=SUMPRODUCT(($B$2:$B$222="Male")*($C$2:$C$222=65)*($D$2:$D$222="*M65*"))

Adam

[PDuPre']

Questions similar to
http://www.mrexcel.com/board/viewtop...9117&forum=2&0

Please note that your columns are A, B, & C but the formula is pointing to B, C, & D. I think that is just a typo.
The real issue seems to be column C. I can get the formula to work just fine as long as you use the entire content of the C column. So, the ...*($D$3:$D$222="*M65*") part of the formula has an issue.
What you need is to be able to search the contents of the C column to see if M65 exists. Of course, if you are looking for 65 in column B then you would actually be searching for M55 in column C. Another typo.
Sorry I couldn't answer the question, but I hope it helps someone else help you out quickly.

Philip

[Aladin Akyurek]

On 2002-05-22 07:15, ageeb wrote:
Hi
My database consist of 3 columns as follow

A B C

Male 55-64 M65-M55-M45
Male 65 M55-M45

I would like a formula that calculated the number of males with B=65 and C= M65. please do help me, I did try
=SUMPRODUCT(($B$2:$B$222="Male")*($C$2:$C$222="55-64")*($D$2:$D$222="*M65*")), but it didn't work.

Thanks in advance

=SUMPRODUCT(($B$2:$B$222="Male")*($C$2:$C$222="55-64")*(ISNUMBER(SEARCH("M65",$D$2:$D$222)))