Results 1 to 15 of 18
Thread: Excel Function (Excell 2000)

20020613, 14:18 #1
 Join Date
 May 2002
 Posts
 69
 Thanks
 0
 Thanked 0 Times in 0 Posts
Excel Function (Excell 2000)
Here it goes...
Dept Group Divison Officers/ Nonofficers/ fulltime/ temps
9500 9303 9319 1/ 3/ 4/ 0/
9500 9303 9319 0/ 0/ 1/ 0/
9525 9511 9381 0/ 1/ 6/ 27
9525 9511 9381 1/ 0/ 3/ 21
What I need to do is create a function that will categorize the information for me. For example, If the dept = 9500 then I want it to add the officers and nonofficers, BUT ONLY for dept 9500. Therefore, the answer should be 4 but I am getting 6 because it's adding dept 9525. How can I accomplish this? HELP!
Also, every month I will be pasting the new information on the data sheet so I think I should stay away from using ranges since the range may change the following month. What do you think?
My fomula is as follows: =IF(Dept1= "9500",SUM(S2:T20),0)
Thanks a bunch! <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

20020613, 14:45 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Excel Function (Excell 2000)
You might try
=SUM(IF(P2:P2000="9500",S2:T2000,0))
Make this an array formula by confirming it with Ctrl+Shift+Enter instead of Enter. You'll see { and } around the formula.
You can replace 2000 by another number. It's only meant to make sure that you won't get into trouble when you add new info.

20020613, 15:06 #3
 Join Date
 May 2002
 Posts
 69
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Function (Excell 2000)
Thank you so much! You can't believe how long I've been racking my brain with that one. I applaude you <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
My last question to this would be how do I add multiple columns that are not next to eachother using the above example. I know the following formula is wrong but you'll get the idea
{=SUM(IF(D22000=9500,(J2:J20,L2:L20,N2:N20,P2:P20),0))}
Thanks again!!!!! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

20020613, 15:48 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Excel Function (Excell 2000)
I know it's not very elegant, but the best I can think of now is
{=SUM(IF(D22000=9500,J2:J2000,0))+SUM(IF(D22000=9500,L2:L2000,0))+SUM(IF(D22000=9500,N2:N2000,0))+SUM(IF(D22000=9500,P2:P2000,0))}

20020613, 15:56 #5
 Join Date
 May 2002
 Posts
 69
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Function (Excell 2000)
I'll definitely give it a try!
Thanks again!!!!! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

20020614, 05:58 #6
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Function (Excell 2000)
Hi Hans,
My XL2K does not recognize sum.if Is it from an addin?Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20020614, 06:05 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Excel Function (Excell 2000)
Hello Jan Karel,
Stupid of me. I translated from the Dutch version without checking ("SOM.ALS"). It should be SUMIF.
Thanks for pointing it out!
Regards,
Hans

20020614, 06:07 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Excel Function (Excell 2000)
<P ID="edit" class=small>Edited by HansV on 14Jun02 08:07.</P>Or  this is not an array formula 
=SUM.IF(D22000,9500,J2:J2000)+SUM.IF(D22000,9500,L2:L2000)+SUM.IF(D22000,9500,N2:N2000)+SUM.IF(D22000,9500,P2:P2000)
Please note that SUM.IF should be SUMIF  thanks to Jan Karel and sorry for the inconvenience.

20020614, 08:03 #9
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Function (Excell 2000)
Hi Hans,
I use this bit of code to put the formula of the activecell in english onto the clipboard, ready to paste into a message:
Option Explicit
Sub CopyformulaEnglish()
Dim oTemp As New DataObject
Dim sFormula As String
sFormula = ActiveCell.Formula
oTemp.SetText sFormula
oTemp.PutInClipboard
Set oTemp = Nothing
End SubJan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20020614, 08:38 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Excel Function (Excell 2000)
Hello Jan Karel,
Neat! I'll add it to my Personal.xls
Thanks,
Hans

20040613, 10:05 #11
 Join Date
 May 2002
 Location
 Dubai, UAE, United Arab Emirates
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Function (Excell 2000)
G'day Jan
I was hoping to poach your bit of code. I need a sub to paste the textual result of a cell to the windows clipboard. However when I tried it  I got an error regarding New DataObject "User Defined Type Not Defined". I presume I'm missing a reference. Can you assist ?? I'm using Excel XP.
Regards Ken Pascoe

20040613, 11:45 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Excel Function (Excell 2000)
 Activate the Visual Basic Editor
 Select Tools  References...
 Tick the check box for "Microsoft Forms 2.0 Object Library".
 Click OK.
In Excel 97, there was a reference to this library by default, but in Excel 2002 (XP) you must set it yourself.

20040613, 11:57 #13
 Join Date
 May 2002
 Location
 Dubai, UAE, United Arab Emirates
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Function (Excell 2000)
G'day Hans  thanks for the reply.
I can't find the Microsoft Forms 2.0 Runtime Libary in the references list. This is a company PC so I can't add anything that's not there, either. I've hadtrouble in the past with minimalist installations of Office (missing the VBA help) that I eventually chased up but this one is probably not something I can get.
Any other suggestions  or a code alternative?
Thanks, Regards Ken.

20040613, 12:02 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Excel Function (Excell 2000)
Try the Browse... button in the References dialog. The Forms library is a file named fm20.dll. On my system (Windows XP), it is in C:WindowsSystem32. If there is no file fm20.dll on your PC, this method won't work.

20040613, 12:07 #15
 Join Date
 May 2002
 Location
 Dubai, UAE, United Arab Emirates
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Function (Excell 2000)
G'day Hans
I've just searched My Computer (including network drives!) for frm20.dll without success. Also for frm*.dll
Therefore I guess I'm looking for a Code alternative.
Can we unlock my post in the Main Excel forum to see what people say?
Thanks, Regards Ken