Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text in SUMPRODUCT (Excel 2000)

    Can I use a text field in a sumproduct function? I am hoping to use it to exclude from my summary sheet a certain group of transactions that can be identifed by their text description. I was hoping to say something like ...*(Additions!C1:c10000<>"xyz")*...to exclude that particular item. Do-able? Thanks.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text in SUMPRODUCT (Excel 2000)

    Yes, text comparisons can also be used in SUMPRODUCT.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text in SUMPRODUCT (Excel 2000)

    I thought so...so the argument is incorrectly stated. Could I have some guidance on that. I tried it both ways "<> XYZ" and <>"XYZ" without apparent success. Excel help seemed to say text was not. Please let me know which one is correct. I will use ASAP to cleanse the text cells of hidden and etc. stuff...perhaps that's it.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Text in SUMPRODUCT (Excel 2000)

    I had better luck using an array function (which I prefer anyway - but that's just me...)

    Assuming your text values are in B4:B14 with values you want to determine the sumproduct of in C4:C14 and D414, try:
    <pre>=SUM((B4:B14<>"Exclude")*C4:C14*D414) </pre>


    This is an array-formula, and will have to be entered with ctrl-shift-enter, whereupon it will be shown with brackets surrounding the formula.

    I have attached a little demonstration....

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text in SUMPRODUCT (Excel 2000)

    Thank you. I will work with my data and see if I can get it to go. I am not as comfortable with array formulas as I'd like to be, so I'm looking for opportunities to implement them.

  6. #6
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text in SUMPRODUCT (Excel 2000)

    What Sumproduct formula were you using?

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Text in SUMPRODUCT (Excel 2000)

    Bob Umlas has an excellent discussion on array formulas. The Primer by Chip Pearson I would also recommend as excellent.

    Also if you describe what you are after, we could help with a sumproduct formula, though this is only a small subset of what is possible.
    Steve

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Text in SUMPRODUCT (Excel 2000)

    In the Math & Trig Category of the Paste Function Dialog, there is a SUMPRODUCT function that sums the products of multiple arrays!
    Regards,
    Rudi

Posting Permissions

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