about countif (as soon as possible please)

hussainy911

New Member
Joined
Sep 5, 2002
Messages
2
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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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).
 

cgmarc

New Member
Joined
Dec 9, 2002
Messages
6
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203

ADVERTISEMENT

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

New Member
Joined
Dec 9, 2002
Messages
6
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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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
ABCD
1123
22
3
4
5
6
7
8
9
1012323.89
11123
1212332.45
1312467.9
14
Sheet1
 

cgmarc

New Member
Joined
Dec 9, 2002
Messages
6
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

Well-known Member
Joined
May 20, 2002
Messages
890
cgmarc

I've expanded a bit....still works...Aladin's hardly ever wrong! :biggrin:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,185
Messages
5,640,682
Members
417,160
Latest member
Timon82

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top