+ Reply to Thread
Results 1 to 14 of 14

SUMIFS furmula

  1. #1
    Registered User
    Join Date
    02-06-2011
    Location
    Tel aviv, Israel
    MS-Off Ver
    Excel 2013
    Posts
    59

    SUMIFS furmula

    Hello,
    I have a table which I wish to do a SUMIFS forumula on it.
    The table goes somthing like this:

    A. Grade 100
    A. Precent 10%
    B. Grade 80
    B. Precent 50%
    C. Grade 20
    C. Percent 12%
    D. Grade 50
    D. Precent 75%

    For each Type I have two Categories: Grade & Precent.

    i wish to have a SUMIFS forumula that sums up only the Grades which are under 15% percent (for example) - Meaning, in this tamplate = 100 (A) + 20 (C)
    thanks.

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: SUMIFS furmula

    Try

    =SUMIFS(A1:A4,B1:B4,"<="&15%)

  3. #3
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: SUMIFS furmula

    Hi atlant15

    =SUMIF(criteria_range,"<0.15",sum_range)

    Or do you mean Count!

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: SUMIFS furmula

    Try

    =SUMIF(B2:B8,"<15%",B1:B7)
    HTH
    Regards, Jeff

  5. #5
    Registered User
    Join Date
    02-06-2011
    Location
    Tel aviv, Israel
    MS-Off Ver
    Excel 2013
    Posts
    59

    Re: SUMIFS furmula

    Hi,
    Thanks for the quick reply.

    it doesn't work because the problem is that the numbers (which i want to sum) are above the %, meaning i cannot have 0.15 as the only crateria.

    Maybe it's not understood but the type (A.. B... C... D...) is in culumn A, the 'Grade' and 'Percent' are in culumn B, and the actual numbers and % are in Culumn C.

  6. #6
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: SUMIFS furmula

    Just adjust my formula to the other columns.

  7. #7
    Registered User
    Join Date
    02-06-2011
    Location
    Tel aviv, Israel
    MS-Off Ver
    Excel 2013
    Posts
    59

    Re: SUMIFS furmula

    still doesn't work

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: SUMIFS furmula

    Does this work for you?
    Attached Files Attached Files

  9. #9
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: SUMIFS furmula

    Hi atlant15

    Do you mean like the attached!
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    02-06-2011
    Location
    Tel aviv, Israel
    MS-Off Ver
    Excel 2013
    Posts
    59

    Re: SUMIFS furmula

    Great... thank you,

    i'll make it a bit harder though, what if i had another % Row in every Type (A... B...) that caculates something else, but i still would want the 15% from the 'percent' category.
    something like this:

    A. Grade 100
    A. Percent 10%
    A. Percent #2 25%
    B. ....

  11. #11
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: SUMIFS furmula

    What would be best, post a sample workbook with your exact requirements and the expected results.

  12. #12
    Registered User
    Join Date
    02-06-2011
    Location
    Tel aviv, Israel
    MS-Off Ver
    Excel 2013
    Posts
    59

    Re: SUMIFS furmula

    Quote Originally Posted by jeffreybrown View Post
    What would be best, post a sample workbook with your exact requirements and the expected results.
    you are right;
    it kind of grew more during the night....
    attached with explenation for what i look for
    Attached Files Attached Files

  13. #13
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: SUMIFS furmula

    how about with if statements???

    =IF(D4<15%,D2,0)+IF(D10<15%,D8)
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  14. #14
    Registered User
    Join Date
    02-06-2011
    Location
    Tel aviv, Israel
    MS-Off Ver
    Excel 2013
    Posts
    59

    Re: SUMIFS furmula

    Well, i thought about doing that, but i got tons of lists like that...
    that's why i'm looking for some kind of overall forumula

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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