brucec@astroatc.UUCP (06/22/87)
Hi, I am using Excel on the Mac & I am having problems with using text instead of numbers. I have a list of items & numbers that are either S(mall), M(medium) of L(arge) and either N(ew) , O(ld) or blank for not there. As a sample I would have: 1 S N 2 S O 3 M 4 S N 5 L N I would like to be able to calculate the number of OLD: S,M,L and New S,M,L. I tried to use the function "=IF($B1="S",IF($C1="O",1)) on each line (on $D, for example) and sumed up all of the 1's in $D but I doubled the amount of data needed (I had similar functions in the $E, $F, $G columns). Q: Is there an easy (or hard) way to match up 2 numeric fields and add up the number of matches?????? Do I need to write a macro? Any leads will be greatly appreciated. -- |{seismo,harvard}!uwvax!astroatc!brucec "A person is only as big as the |{ihnp4,decvax}!nicmad!astroatc!brucec things that make him angry."
graifer@net1.ucsd.edu (Dan Graifer) (06/22/87)
There are two easy ways to do what you want to do (count by two criteria).
A minor modification of your method would be to use a single column of
D1:IF($B1 = $F$1, IF($C1 = $G$1,1,0),0)
and use a TABLE(F1,G1) on SUM(D1:D5).
Or you can avoid the column of ifs by using Excel's database functions. I
have enclosed a Binhex 4.0 example.
Dan Graifer
graifer@net1.UCSD.EDU
Disclaimer: Nobody ever listens to me anyways; Why should they start now?
---------------------------<CUT HERE>--------------------------------------
(This file must be converted with BinHex 4.0)
:%89iB@e`E'8JCQpb)%*bG@0P!&K-3NjB3d9-!*!%#P8!N!4dGP-R!"'V*`#3"`)
$*J!B!"B$2J08!!lqZ!!$!!i!X3*eFJS!#K9H'G%$AKRGB@"JAKD3!!"RB*!$Iim
`B@"J%hpX&T!!!(i5MaD&IK"X)T!!!(iGMaD'IKYI!RCkN!!!ILU2&QfXHUeJIf#
3!j!!!(ijN!!!IMf3!!"q3C!!!(j&N!!!INQ2BG8@Khj3Mc%8Gf!@Ifq&&'VA&Rp
ZV(UYIQ53!!"qD*!!!(jXMf(9&R+&IR52ILCcIL*bV(UYIS#2BG8@F)9qL)pq1R&
q0R"qC0d!''-QAS!M%"+pB"&X()@a!8"R%@`DV(UY%981%mm$X3&p%98+%98*cJ1
a!4i493d493c1!l%"!4&9#L-2%980*4&B"q849qAQ"!MPi`RPjJ3'jHB&fRSR"q8
49qAQ"!MPi`RPjJ6DGf!SB3S4@3YrN!!!D"&X(Q0JN!!!%9S493pSehSU%9X493p
h)',9HK&9%(FmHLX494"h+Gl&!&"4Di0BD'%0$Q"rN!!!D"&X*'4JN!!!B3d1BT!
!!"'N*Yl#!"%$#E!TD#aR,Aq`)Q%XCbf`('%0$Pi1N!!!B*!%IimYB3d1%'`2N!!
!IK'2,@FXX,5`3')XB5eqLj!!!,#SIT&eN!!!IMi493phH,TK,,#AIQU3!!!9CFi
,D"CcC'#3!!"qHC!!!'NXC#f`IQFXIS@3!!"q9BmYX(*R%@`DV(UYXIld%98,%@`
DKApR%@`8V(UYXIlACa&X(+akV4&9$K244"3493jSeAST$&B4G)8-9a&X%S9r$&J
4E"D&$&N4E"L&Ia&9#4&@hmm&%9E5IKm493`49pr2"4&AdRiI%98)%@`8KAjMXIj
kB0d2&Qq&%"D(%98+&S8493d@KK9Mc`4J&R#&&@V2"'!@FS9Rh3!4%F"J%'`XK@%
3A""G%&8@Ij!!!'#3"(q2+`l*B0eq$BmUIK'2G`Dj#Q%3E#bXHUerX'X0Z@J1%Y"
NILQ2hX%X!b'`@QGJN!0rMb`2Gajrb%d2Gp!@EBN@KAj)MaC[L4D(X#*q)Bpq(XJ
d$hI32DdpIhi[MhiXb#Cq%(jTMcfY2Aj[MaD&IRm0HQk3!!"S%`jJN!!!$K5&$Je
kERpqFQIGIJYqH(PNh3!!!cJF4!3!!"13!!!)rV`!!3!)!!MG!!!%iN*["!!!!bB
!d!"N!!%!!3!"!!!r8'*0d['Tr!#3!`m!r`#3#`)!![rr!!$Aj3!%!*!%rrq!$2r
r!!EAi!!'!*!$!3",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`"
,!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`"
,!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`"
,!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`"
,!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`"
,!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`"
,!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`"
,!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`"
,!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`"
,!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`"
,!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!%X!5`",!*!$!`!
+!!8!$!!0!!S-q!!)!!IB$`!-!*!-er%!$!#3#`33!!)"m!!$!"B!!`!f!!-"RJ!
$!Ci!!`([!!8!&`!&!&)!!3!6!J#3!`q3!`%*$94KBQaP)%9iB@e`E'8!N!-(N!-
"#398B@*XC3#3)JB!9"3!!&B!8"MAl`!!3!$Al`!!3!#3!jB!9)3!!,B!8!J!!0B
!3!$Ak`!!3!!!!8B!9!3!!@B!8!J!!BB!3!$Ad3!!3!!!"(B!9!3!"*B!8!J!",B
!3!!""J!!3!!!"kB!9!3!"mB!8!J!"qB!3!!!!`!!3!!!#XB!9#3!#ZB!8#J!#`B
!3!$Al`!!3!!!!`!!3!!""J!!3!!!$TB!3!#3"8!!!"%@!&38!"&Q!&!Ber$!!%!
!er!!!%!!!"'Q!&3N!"('!&!S!*!%3!#3"8!!ep-!!%!!N!9!!0I5!!"!3!#3"%!
!ep2J!%!8!*!%3"!!N!4!%!#3"%!B!"(U9&"%!"4'!%3!!"4Q!%3!!"5'!%3)!"5
Q!%J%!"6+!%!!!"G+!%!!!"LU!%!)!"S'!%J%!"SU!%!!!"Z+!%!!!"cU!%!)!"j
'!%JN!"jU!%!J!"r+!%!J!#%U!%!S#!F%,3!(!!J!!3G$FQPdCA*TB3J("#d!N!-
&!!%(4'&dB@*KFf8+!*!$8(*TER4I3A*PB3`!N!03FQPZG&p8DA4XCA-)!*!$8Q9
MEh*NCA)!"&0*@N8$38G&!9-"6JC1Eh4PFcS"8`&2,M%T)%0PE'ac)%)d)'&ZC#"
"-68JBfpZG'&TEL"K)'*XB@jV,#"16e3J48e39&N"63%J,M)T)%0PE'`J36%b)'K
KFb"MFQPdD@0KE#"QEh*YG@aK,#"QG@jZH5"'6e*0393"8`&1,6-T)&P[G5"MEh9
XC#"eFf8J-5"MEfaeE@iJEfBJH@peFL"*4L"QG@jMG'P[EJ&-!8ii)*!&BR9d)(9
cC5"K)'0PE'`JFQ9Q)'C[FL"dD'8JG'&bCf9d)(CKE(9PFb`JB@jN)(9cC5"K*b#
3"A4KBQaP)(4[)(0eE5"QEh)JC'PQCQ9bC@jd)(4KFQGPG(-%8dPD430"4d8"2`%
r3"3!N!X,%b2Ak3$B%!N(&L2Aj36B%3F335J!!J!"!9-"63&-!8mrm!#3#3%!$!3
#eq!'3!#3!``!$J%$!!!+!!!Jrrr!!3!J!*!+e`)!$!3#eq!'!!`"!*!+e`-!$!3
#eq!'!!`"!8j!!*!*#J%!$33#eq!'!!`"!*!+e`)!$33#eq!'!!`"2r!!N!N$!!d
%!YIJ"J!-!3%J!*!,!3!1"!,Ai!B!$!%rm!#3#3)!$J3#eq!'!!`"!*!,!`!1"!,
Ai!B!$!(r!$d!#3&2!IB%(3#3"K)!%J!!"3B!"3!'!*!%er%%!!%"f!d'!*!%#J#
3"3%!#J!+"!3!N!3r#J!!!:
Dan Graifer
graifer@net1.UCSD.EDU
Disclaimer: Nobody ever listens to me anyways; Why should they start now?