#### hussainy911

##### New Member
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

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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).

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.

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

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?

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.

It should be...

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

Book2
ABCD
1123
22
3
4
5
6
7
8
9
1012323.89
11123
1212332.45
1312467.9
14
Sheet1

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.

cgmarc

I've expanded a bit....still works...Aladin's hardly ever wrong!
Book4
ABCD
1123\$200.004
2123
3123\$1.00
4122\$6.00
5121\$2.00
6120
7123\$5.00
8123\$6.00
Sheet1

Post the formula you're using

Replies
1
Views
224
Replies
1
Views
305
Replies
13
Views
2K
Replies
1
Views
196
Replies
12
Views
938

1,220,979
Messages
6,157,184
Members
451,403
Latest member
scorpidxls

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back