# Thread: Excel Function (Excell 2000)

1. ## Excel Function (Excell 2000)

Here it goes...
Dept Group Divison Officers/ Non-officers/ 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 non-officers, 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>

2. ## 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.

3. ## 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>

4. ## 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))}

5. ## 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>

6. ## Re: Excel Function (Excell 2000)

Hi Hans,

My XL2K does not recognize sum.if Is it from an add-in?

7. ## 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

8. ## Re: Excel Function (Excell 2000)

<P ID="edit" class=small>Edited by HansV on 14-Jun-02 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.

9. ## 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 Sub

10. ## Re: Excel Function (Excell 2000)

Hello Jan Karel,

Neat! I'll add it to my Personal.xls

Thanks,
Hans

11. ## 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

12. ## 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.

13. ## 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.

14. ## 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.

15. ## 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

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•