Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Star Lounger
    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/ 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. #2
    Plutonium Lounger
    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.

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

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

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

  6. #6
    Platinum Lounger
    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 add-in?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

  8. #8
    Plutonium Lounger
    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 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. #9
    Platinum Lounger
    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 Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Plutonium Lounger
    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

  11. #11
    2 Star Lounger
    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

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

  13. #13
    2 Star Lounger
    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.

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

  15. #15
    2 Star Lounger
    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

Page 1 of 2 12 LastLast

Posting Permissions

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