+ Reply to Thread
Results 1 to 22 of 22

Find the total sum of Multiple category of value.

  1. #1
    Registered User
    Join Date
    09-29-2022
    Location
    Pune
    MS-Off Ver
    2019
    Posts
    13

    Question Find the total sum of Multiple category of value.

    Hey there,

    I've attached sheet with it.

    I am trying to figure out the total value of sales for a particular salesman of ST products based on criteria..

    In the right side ST Products is given, and in the middle sales data is given..

    Salesman suresh have done both the category of st product so i only want to add those values.

    How to do that?
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Find the total sum of Multiple category of value.

    Welcome to the forum.

    Try this:

    =SUM(SUMIFS($L$7:$L$11,$J$7:$J$11,C9,$K$7:$K$11,$P$8:$P$9))

    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Find the total sum of Multiple category of value.

    Join your two tables in Power Query

    Please Login or Register  to view this content.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Find the total sum of Multiple category of value.

    Another option:

    =SUMPRODUCT(SUMIFS(L$7:L$11,J$7:J$11,C9,K$7:K$11,P$8:P$9))

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find the total sum of Multiple category of value.

    with Power Query I suggest this one

    Please Login or Register  to view this content.
    Name St Amount
    Suresh Kohli
    700

  6. #6
    Registered User
    Join Date
    09-29-2022
    Location
    Pune
    MS-Off Ver
    2019
    Posts
    13

    Re: Find the total sum of Multiple category of value.

    Thankyou so much mam, I got my answer. Thankyou once again

  7. #7
    Registered User
    Join Date
    09-29-2022
    Location
    Pune
    MS-Off Ver
    2019
    Posts
    13

    Re: Find the total sum of Multiple category of value.

    Thankyou so muchh,, It works for me

  8. #8
    Registered User
    Join Date
    09-29-2022
    Location
    Pune
    MS-Off Ver
    2019
    Posts
    13

    Re: Find the total sum of Multiple category of value.

    Thankyou so much. It works for me. Thankyou for the help

  9. #9
    Registered User
    Join Date
    09-29-2022
    Location
    Pune
    MS-Off Ver
    2019
    Posts
    13
    Quote Originally Posted by AliGW View Post
    Welcome to the forum.

    Try this:

    =SUM(SUMIFS($L$7:$L$11,$J$7:$J$11,C9,$K$7:$K$11,$P$8:$P$9))

    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Thankyou so much mam for the help.
    just one more question that how to solve that if i want to exclude that ST product only and sum others?
    Last edited by ishaqib007; 09-29-2022 at 09:24 AM.

  10. #10
    Registered User
    Join Date
    09-29-2022
    Location
    Pune
    MS-Off Ver
    2019
    Posts
    13
    Quote Originally Posted by 63falcondude View Post
    Another option:

    =SUMPRODUCT(SUMIFS(L$7:L$11,J$7:J$11,C9,K$7:K$11,P$8:P$9))
    Thankyou so much. It work

  11. #11
    Registered User
    Join Date
    09-29-2022
    Location
    Pune
    MS-Off Ver
    2019
    Posts
    13
    Quote Originally Posted by sandy666 View Post
    with Power Query I suggest this one

    Please Login or Register  to view this content.
    Name St Amount
    Suresh Kohli
    700
    Thankyou for your response.. Done with the question

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Find the total sum of Multiple category of value.

    Glad to have helped.

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

  13. #13
    Registered User
    Join Date
    09-29-2022
    Location
    Pune
    MS-Off Ver
    2019
    Posts
    13

    Re: Find the total sum of Multiple category of value.

    Quote Originally Posted by AliGW View Post
    Glad to have helped.

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
    Sure mam, i'll do that.. Just one more thing how do i calculate the same but exclude ST products?

  14. #14
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find the total sum of Multiple category of value.

    Quote Originally Posted by ishaqib007 View Post
    Thankyou for your response.. Done with the question
    My pleasure

  15. #15
    Registered User
    Join Date
    09-29-2022
    Location
    Pune
    MS-Off Ver
    2019
    Posts
    13
    Quote Originally Posted by sandy666 View Post
    My pleasure
    Just one more help please
    What about if i want to exclude that ST products and want to some others value how will i do that?

  16. #16
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find the total sum of Multiple category of value.

    try

    Please Login or Register  to view this content.
    Name St Amount
    Ajay
    500
    Suresh Kohli
    700


    and filter name from Name column

    filter.png
    Last edited by sandy666; 09-29-2022 at 04:46 PM.

  17. #17
    Registered User
    Join Date
    09-29-2022
    Location
    Pune
    MS-Off Ver
    2019
    Posts
    13
    Quote Originally Posted by AliGW View Post
    Welcome to the forum.

    Try this:

    =SUM(SUMIFS($L$7:$L$11,$J$7:$J$11,C9,$K$7:$K$11,$P$8:$P$9))

    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Please tell me how can i do the same if i want to get the total amount excluding ST products?

  18. #18
    Registered User
    Join Date
    09-29-2022
    Location
    Pune
    MS-Off Ver
    2019
    Posts
    13
    Quote Originally Posted by sandy666 View Post
    try

    Please Login or Register  to view this content.
    Name St Amount
    Ajay
    500
    Suresh Kohli
    700


    and filter name from Name column

    Attachment 798451
    Thanks for answer 😅 but i don't understand how i works so can we do the same thing by using formula using sumifs

  19. #19
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Find the total sum of Multiple category of value.

    Quote Originally Posted by ishaqib007 View Post
    so can we do the same thing by using formula using sumifs
    sure, probably you can but maybe someone else will give this kind of solution

  20. #20
    Registered User
    Join Date
    09-29-2022
    Location
    Pune
    MS-Off Ver
    2019
    Posts
    13

    Re: Find the total sum of Multiple category of value.

    Quote Originally Posted by sandy666 View Post
    sure, probably you can but maybe someone else will give this kind of solution
    Okay thanks for your response again

  21. #21
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Find the total sum of Multiple category of value.

    Just
    =Overall Sum - STsum

    With overall sum = SUM(L7:L11) and STsum is current STsum you are using
    Quang PT

  22. #22
    Registered User
    Join Date
    09-29-2022
    Location
    Pune
    MS-Off Ver
    2019
    Posts
    13

    Re: Find the total sum of Multiple category of value.

    Quote Originally Posted by bebo021999 View Post
    Just
    =Overall Sum - STsum

    With overall sum = SUM(L7:L11) and STsum is current STsum you are using
    Got your point thanks alot sir ..

+ 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. Individual Category Sales Total and Main Category Sales Total
    By otisotis in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-31-2022, 03:51 PM
  2. [SOLVED] % of category total
    By oO P2K Oo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-11-2017, 12:03 PM
  3. [SOLVED] Total count by category
    By treyk in forum Excel General
    Replies: 3
    Last Post: 10-04-2016, 03:20 AM
  4. Formula to multiply total number per category with the % of total
    By tantcu in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-13-2013, 07:18 PM
  5. Replies: 2
    Last Post: 09-14-2012, 04:31 AM
  6. Using VLOOKUP to find multiple occurrences and find the Total quantity
    By susanpa in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-30-2009, 09:54 AM
  7. Replies: 4
    Last Post: 10-18-2006, 12:25 PM

Tags for this Thread

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