about countif (as soon as possible please)

[hussainy911]

CAT.... TYPE
B...... C
B...... L
B...... L
V...... F
B...... L
B...... R


We have Two coulomns CAT & TYPE.What I want is that I want to count the number of employees with CAT "B" & TYPE "L"....
This message was edited by hussainy911 on 2002-09-06 18:34

 

[Aladin Akyurek]

On 2002-09-06 18:32, hussainy911 wrote:
CAT.... TYPE
B...... C
B...... L
B...... L
V...... F
B...... L
B...... R


We have Two coulomns CAT & TYPE.What I want is that I want to count the number of employees with CAT "B" & TYPE "L"....
This message was edited by hussainy911 on 2002-09-06 18:34

=SUMPRODUCT(($A$1:$A$6=E1)*($B$1:$B$6=F1))

where E1 houses a CAT-value like "B" and F1 a TYPE-value like "L" (without quotes).

 

[hussainy911]

Thnx Alot Aladin Akyurek

 

[cgmarc]

I keep searching, and I think this and the similar array (CSE) formula I see in other posts should fix my problem, but no joy.

Here it is: A1:J1 has 10 different numeric codes (123, 125, 129, 138 etc.)

A10:A100 has those codes, sorted by type.
B10:B100 has account balances. (can there be empty cells?)

In Cell A2, I want to count the number of Balances >0 for the code in A1 (123)

Thanks.

 

[Aladin Akyurek]

On 2002-12-10 13:48, cgmarc wrote:
I keep searching, and I think this and the similar array (CSE) formula I see in other posts should fix my problem, but no joy.

Here it is: A1:J1 has 10 different numeric codes (123, 125, 129, 138 etc.)

A10:A100 has those codes, sorted by type.
B10:B100 has account balances. (can there be empty cells?)

In Cell A2, I want to count the number of Balances >0 for the code in A1 (123)

Thanks.

=SUMPRODUCT(($A$10:$A$100=A$1)*(LEN($B$10:$B$100)=0))

 

[cgmarc]

AA,
I still get 0. I even created the generic scenario I gave you. I looked up LEN, because I've seen it before, but didn't know what it was for. Excel Bible says it returns # of chars in text. How are you using it?

 

[cgmarc]

OK, I got it. Found the answer at http://www.mrexcel.com/tip031.shtml
It's an array (CSE):

=SUM(IF(A10:A100=A1,IF(B10:B100>0,1,),0))

Hope it works for my real problem.

 

[Aladin Akyurek]

On 2002-12-10 14:46, cgmarc wrote:
OK, I got it. Found the answer at http://www.mrexcel.com/tip031.shtml
It's an array (CSE):

=SUM(IF(A10:A100=A1,IF(B10:B100>0,1,),0))

Hope it works for my real problem.

Sorry, I misread your question. No LEN is neccessary...

It should be...

=SUMPRODUCT((A10:A13=A1)*(B10:B13>0))

Adjust the ranges to suit.

Book2
	A	B	C	D
1	123
2	2
3
4
5
6
7
8
9
10	123	23.89
11	123
12	123	32.45
13	124	67.9
14
Sheet1

 

[cgmarc]

AA: I tried your solucion in my sample problem, and it gives the wrong answer. It should be 11, but it gives 15.

I have tried my solution that works for the sample scenario in my actual spreadsheet, but IT doesn't work there. I spits out 0. Does Excel ever get stuck? I've tried slapping and kicking it. Anything else I can try. I'm having trouble with the HTML add in, and I can't email my spreadsheet to you, but if you expand your scenario, you'll find that it's calculating the wrong answer.

 

[maxflia10]

cgmarc

I've expanded a bit....still works...Aladin's hardly ever wrong!

Book4
	A	B	C	D
1	123	$200.00	4
2	123
3	123	$1.00
4	122	$6.00
5	121	$2.00
6	120
7	123	$5.00
8	123	$6.00
Sheet1

Post the formula you're using