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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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).
 
Upvote 0
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.
 
Upvote 0
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))
 
Upvote 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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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
Back
Top