formula

[ch1]

Help please, new to Excel.
If in
column A and in Column B
1 fred
1 bill
1 bill
3 fred
4 dave
5 bill

how do I get a cell to show a count of Bill only when 1 in column A

result = 2

Hope that makes sence

thanks

[ This Message was edited by: ch1 on 2002-05-11 15:51 ]

[Aladin Akyurek]

On 2002-05-11 15:50, ch1 wrote:
Help please, new to Excel.
If in
column A and in Column B
1 fred
1 bill
1 bill
3 fred
4 dave
5 bill

how do I get a cell to show a count of Bill only when 1 in column A

result = 2

Hope that makes sence

thanks

[ This Message was edited by: ch1 on 2002-05-11 15:51 ]

=SUMPRODUCT((A2:A7=E1)*(B2:B7=E2))

where A2:A7 houses the numbers, B2:B7 the names, E1 the criterion number 1, and E2 the criterion name Bill.

[Tom Schreiner]

Hi Aladin....
Why isn't this formula working?

=SUMPRODUCT(COUNTIF(B:B,"bill")*COUNTIF(A:A,1))

I listed them seperately and these are the results.

COUNTIF(B:B,"bill") = 0
COUNTIF(A:A,1)) = 3

Thanks,
Tom

[ This Message was edited by: TsTom on 2002-05-11 16:04 ]

[ch1]

Thanks, I have tried that and get a result of 0.

??????????????

[sen_edp]

On 2002-05-11 16:00, Aladin Akyurek wrote:

=SUMPRODUCT((A2:A7=E1)*(B2:B7=E2))

where A2:A7 houses the numbers, B2:B7 the names, E1 the criterion number 1, and E2 the criterion name Bill.

I tried Aladin's formula and it works.
I use xl2000 with windows2000 prof

[Aladin Akyurek]

[i]
Why isn't this formula working?

=SUMPRODUCT(COUNTIF(B:B,"bill")*COUNTIF(A:A,1))

I listed them seperately and these are the results.

COUNTIF(B:B,"bill") = 0
COUNTIF(A:A,1)) = 3
[i]

Tom,

That formula "works", not the way we want.
It would result in 9, because
COUNTIF(A:A,1) ==> 3 and
COUNTIF(B:B,"bill") ==> 3.

Hence:

=SUMPRODUCT(3*3) ==> 9

What is desired is something different:

=SUMPRODUCT((A2:A7=1)*(B2:B7="Bill"))

works, put in stages, as follows:

eValuation Stage:

=SUMPRODUCT({TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}*{FALSE;TRUE;TRUE;FALSE;FALSE;TRUE})

Multiplication (PRODUCT) Stage:

=SUMPRODUCT({0;1;1;0;0;0})

Addition (SUM) Stage:

=2

For more on this, see:

http://www.mrexcel.com/wwwboard/messages/8961.html

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-05-11 16:41 ]

[Aladin Akyurek]

On 2002-05-11 16:30, ch1 wrote:
Thanks, I have tried that and get a result of 0.

??????????????

Try again... The one I suggested should work.

[ This Message was edited by: Aladin Akyurek on 2002-05-11 16:41 ]

[ch1]

Thanks

Typed it in again and it worked.

I said I was new, just proved it

Many thanks

[Tom Schreiner]

Thanks Aladin