+ Reply to Thread
Results 1 to 4 of 4

sumproduct to count special content from a table

Hybrid View

  1. #1
    Registered User
    Join Date
    09-05-2013
    Location
    Vienna, Austria
    MS-Off Ver
    Excel 2010
    Posts
    32

    sumproduct to count special content from a table

    hello togehter^^^

    i use a sumproduct to count a special content from a table.

    I have the country, a special key and the product.

    I have different products like rismo 12, rismo 100, rismo 200.

    But i want to count them all.

    so i have the following function.

    =+SUMPRODUCT(((ISNUMBER(FIND("rismo";E1:E1000))*1)*(C1:C1000=D2)))*((H1:H1000=60)+(H1:H1000=65)).

    In the cells c are the countrys. In the cells h are the special keys. I am looking for the 60 and 65.

    My result is allways 0. Does someone know why?

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: sumproduct to count special content from a table

    Hi,

    Could be several reasons. I notice you're using FIND, which is case sensitive - have you double-checked that E1:E1000 contain "rismo" in that precise form, with no extra spacing? If you want to include examples of "Rismo" or "RISMO" as well, replace FIND() with SEARCH(), which is case insensitive.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,156

    Re: sumproduct to count special content from a table

    hi benjamin. another reason could be that this *((H1:H1000=60)+(H1:H1000=65)) is not part of your SUMPRODUCT. you close the brackets at the wrong places.
    =SUMPRODUCT((ISNUMBER(FIND("rismo";E1:E1000)))*(C1:C1000=D2)*((H1:H1000=60)+(H1:H1000=65)))

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Registered User
    Join Date
    09-05-2013
    Location
    Vienna, Austria
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: sumproduct to count special content from a table

    Hello thanks for your help. Now my problem got even bigger.

    I want to count all the data with the following infomation

    - a special country (e.g. Austria)
    - special key (here 60 or 65)
    - the name of the product (here i have the problem with rismo 6, rismo 100, rismo 200; i want that it counts all the rismo)
    - the date (e.g. just show them from april)

    How can i solve this problem?

    Greeting benjamin

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Vertical to Horizontal Data Content w/ DeDuped Special Conditions 2
    By guypier in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-09-2015, 11:23 AM
  2. Paste Special Content: with 5 product and paste special content
    By Cuong Pham in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-27-2013, 11:50 PM
  3. Auto update cells in a table, based on the content of another table...
    By Darth269 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-29-2012, 04:19 PM
  4. Using a SumProduct Count to find a SumProduct Total?
    By XL021710 in forum Excel General
    Replies: 3
    Last Post: 02-18-2010, 08:31 AM
  5. special case of sumproduct
    By delali in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-24-2006, 02:25 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1