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-12-11 12:34, cgmarc wrote:
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.

Do you have formulas in the Balance range -- in the xample B10:B13?

 

[cgmarc]

Thanks. Clearly, Aladin's the wiz. Below's the scenario as I had it in Excel.htm.

I need the number of balances that are greater than zero for each account. Thanks.

Any idea why neither solution would work in my actual problem? (the file size is 22Meg?)
Thanks again.

"Orig" is the formula originator, column A
Formula column B
123 etc starts in C1, thru L1
Data set is in C10:E100

orig formula 123 125 167 178 189 190 191 192 195 197
aa1 SUMPRODUCT(($C$10:$C$100=C$1)*(LEN($E$10:$E$100)=0)) 0 0 0 0 0 0 0 0 0 0
CGMARC SUM(IF($C$10:$C$100=C$1,IF($E$10:$E$100>0,1,0),0)) 11 3 8 6 1 11 6 0 5 12
AA2 SUMPRODUCT(($C$10:$C$100=C$1)*($D$10:$D$100>0)) 15 5 13 8 2 9 7 0 7 17




account dummy code balance
123 f 765
123 f 789
123 f 897
123 f 0
123 f 56
123 g 0
123 g 9
123 h 87
123 j 89
123 j 43
123 m 765
123 0
123 654
123 g 0
123 fsd 0
123 0
123 dg 67
123 dg 0
125 dg 865
125 gd 0
125 gd 0
125 g 765
125 yjk 765
167 ryh 78
167 w 0
167 b 3
167 wfg 0
167 w 6
167 w 0
167 t 54
167 hj 76
167 rum 0
167 rj 0
167 mtj 54
167 e 89
167 t 6432
178 ewt 965
178 ty 7
178 r 4873
178 ne 258
178 gb 0
178 et 0
178 eg 567
178 eg 68
189 heh 0
189 nr 356
190 uj 764
190 etg 8952
190 weth 963
190 57909
190 jkuj 9
190 dg 0
190 85
190 n 23
190 74
190 deg 9645
190 0
190 ju 675432
190 re 86
191 yjk 547
191 ey 840
191 ryh 578
191 yjk 0
191 rh 423
191 jru 85
191 r 79
192 0
195 wb 532
195 gh 568
195 r 9645
195 fgh 0
195 d 67
195 yjk 456
195 yuj 0
197 uj 7
197 yuj 54
197 dg 8
197 drwtryw 3
197 t 0
197 h 7
197 y 0
197 j 0
197 y 75
197 gyt 36
197 ye 0
197 tygred 7
197 b 469
197 j 963
197 t 974
197 w 0
197 h 46780

 

[Aladin Akyurek]

On 2002-12-11 13:33, cgmarc wrote:
[...]I need the number of balances that are greater than zero for each account. Thanks.

Any idea why neither solution would work in my actual problem? (the file size is 22Meg?)
Thanks again.
[...]

I assume that the formula in the Balance range does not produce so-called blanks. I believe you have values in there which looks like 0 but they are greater than 0. For example, 0.0000000008. So I suggest trying the following amended formula...

=SUMPRODUCT(($C$10:$C$100=C1)*(ROUND($E$10:$E$100,2)>0))

which rounds the balance values to 2 decimals.

Regarding the 2 meg question: Since you don't have too many account codes, you shouldn't notice any significant performance degradation.

Aladin

 

[cgmarc]

Ok, I understand the logic now. But it's not working for an entirely different reason in my actual problem.

For some reason, when the account codes in C10:C100 (in the example we're working with) do equal (Say C10:C17 all do in fact do have the same value as C1)the code referenced in C1, Excel returns Boolean false. I checked it out with =if(C10=C1,"Cool","Crap"). It returned Crap. If I go into the cell and type in the value, I returns Cool. I can't do that for thousands of records. C1 and C10:C100 are all formatted as General. ???thanks.

Well, I just found the answer in a different post: http://www.mrexcel.com/board/viewtopic.php?topic=33443&forum=2&9 I love this site!
This message was edited by cgmarc on 2002-12-19 08:23

 

[Aladin Akyurek]

On 2002-12-19 08:11, cgmarc wrote:
Ok, I understand the logic now. But it's not working for an entirely different reason in my actual problem.

For some reason, when the account codes in C10:C100 (in the example we're working with) do equal (Say C10:C17 all do in fact do have the same value as C1)the code referenced in C1, Excel returns Boolean false. I checked it out with =if(C10=C1,"Cool","Crap"). It returned Crap. If I go into the cell and type in the value, I returns Cool. I can't do that for thousands of records. C1 and C10:C100 are all formatted as General. ???thanks.

C-entries have probably extraneous spaces...like in

"Cool " instead of "Cool"

If so, apply TRIM to these entries or use free ASAP Utilities from

http://asap-utilities.com