+ Reply to Thread
Results 1 to 16 of 16

counting the total number of items which are ordered

  1. #1
    Registered User
    Join Date
    05-16-2015
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    8

    counting the total number of items which are ordered

    Hello all,

    I am struggling to find the right formula.
    In column A I have a bunch of article numbers. Some of these article numbers are in this column more then once (on different orders).
    In column B I have the amount of the articles I ordered of each order.
    What I am looking for is a formula to calculate the total amount of each item I ordered.
    So when I ordered an item once, it's no problem.
    But when I ordered an item on 2 orders, lets say the first time 2 pieces and the second time 3 pieces, I want to see I have ordered a total amount of 5 pieces for this item.
    And when I ordered an item on 3 orders, lets say the first time 3 pieces, the second time 1 piece and the third time 2 pieces, I want to see I have ordered a total amount of 6 pieces for this item.
    Etcetera...

    Hopefully I explained it well.
    Last edited by ImTr0uBLe; 05-16-2015 at 09:54 AM.

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: counting the total number of items which are ordered

    Have you tried SUMIF or SUMIFS?
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  3. #3
    Registered User
    Join Date
    05-16-2015
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    8

    Re: counting the total number of items which are ordered

    No I didn't try that. What would the formula look like in the above example?
    Note: the article numbers will vary, so I can not give a specific article number in the formula.
    I found the following formula to count the amount of times each article number appears in column A:
    =ALS(AANTAL.ALS($A$2:A2;A2)=AANTAL.ALS(A:A;A2);AANTAL.ALS(A:A;A2);"")
    That is Dutch and I think AANTAL.ALS should be COUNT.IF in english.
    So now I can see how many times each article number exists in column A and I hoped it would help me on my way, but I am stuck.
    Last edited by ImTr0uBLe; 05-16-2015 at 10:00 AM.

  4. #4
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: counting the total number of items which are ordered

    Not sure, I think it may need to be a few different functions to get it to work

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: counting the total number of items which are ordered

    See attached for example of SUMIF
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2010 plus
    Posts
    730

    Re: counting the total number of items which are ordered

    Pivot table is an other solution.
    It can make a list of order No and amount ordered.
    It is on insert tab
    Mark the thread as solved if you are satisfied with the answer.


    In your first post under the thread tools.

    Mahju

  7. #7
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: counting the total number of items which are ordered

    John,

    Thank you for helping. I was trying to do a SUMIF for the OP, but failed....I didn't even thing about adding a helper column with the Part(Article) column.

  8. #8
    Registered User
    Join Date
    05-16-2015
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    8

    Re: counting the total number of items which are ordered

    Thanks for the replies. But still I have to fill in the article numbers with the sample solution and the sheet exists of thousands of article numbers and they will vary every time I update.
    I tried to make a pivot table and I am also not able to see what I want to see, but that can be because I am not that experienced yet in excel.
    And my thoughts were also that I maybe needed a few different functions to get it to work, thats why I started with the formula above.
    I will see if I can manage to post an example.
    Last edited by ImTr0uBLe; 05-16-2015 at 10:36 AM.

  9. #9
    Registered User
    Join Date
    05-16-2015
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    8

    Re: counting the total number of items which are ordered

    Ok, I think here is my example if I did it right.
    But this is a simple example, in reality the sheet is much larger.
    Attached Files Attached Files

  10. #10
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: counting the total number of items which are ordered

    Im confused what you after, can you post file with your expected results?

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: counting the total number of items which are ordered

    Try this in column C:

    =IF(COUNTIF($A$2:A2,A2)=COUNTIF(A:A,A2),SUMPRODUCT(($A$2:$A$30=A2)*($B$2:$B$30)),"")

  12. #12
    Registered User
    Join Date
    05-16-2015
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    8

    Re: counting the total number of items which are ordered

    The expected results are colored.
    Keep in mind that the article numbers and amount will change.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    05-16-2015
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    8

    Re: counting the total number of items which are ordered

    @JohnTopley: I tried to copy paste your formula in the example, but it gave me an error, so I translated it to dutch, but still it gave me an error. Can you please try your formula in my example sheet, because it looks promising.

  14. #14
    Registered User
    Join Date
    05-16-2015
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    8

    Re: counting the total number of items which are ordered

    @JohnTopley: GREAT!! I got it to work with your formula
    Thanks a lot!!
    Attached Files Attached Files

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: counting the total number of items which are ordered

    Glad to help. Please mark thread as SOLVED.

  16. #16
    Registered User
    Join Date
    05-16-2015
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    8

    Re: counting the total number of items which are ordered

    Is there a button for that or do I just write..?

    SOLVED.

+ 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. [SOLVED] Counting the number items based on number of days
    By Ltat42a in forum Excel General
    Replies: 6
    Last Post: 10-29-2014, 07:11 PM
  2. Making a list of items, then showing the date the item was last ordered
    By dance in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-30-2014, 06:05 AM
  3. Count Total of items in a column w/o counting duplicates or blanks
    By Clairebear4 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-23-2013, 10:56 PM
  4. Replies: 2
    Last Post: 11-01-2007, 09:32 PM
  5. items to be ordered, help?
    By 207 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-15-2006, 06:00 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