+ Reply to Thread
Results 1 to 6 of 6

SUM values with criteria

  1. #1
    Registered User
    Join Date
    02-01-2021
    Location
    United Kingdom
    MS-Off Ver
    1808
    Posts
    2

    Post SUM values with criteria

    Hello,

    I am trying to create a spreadsheet where it will look for a certain date (could be multiple dates) in a table and then it checks if those dates have a 100% percentage, if it does then it adds up the pay and displays the figure. But I'm not sure what formula to use or how to use it so that it does this. So that the end result under "PAY AMOUNT" will show the amount I have to pay if they were 100% in that month.

    Could someone help, please?

    Kind Regards,
    Jacob
    Attached Files Attached Files
    Last edited by jacobsscoots; 02-01-2021 at 12:32 PM.

  2. #2
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    re: SUM values with criteria

    Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: SUM values with criteria

    Hi. Forum Rule 1 is very short. It states: "Title must briefly summarize your request."

    A GOOD thread title is the sort of thing you would use as a search term on Google*. Your chosen title would produce a squillion useless hits. In future, please take a bit more time to think of a descriptive title that would be "Google-friendly". Many people search the forum answers looking for help and your very general title wouldn't help them at all.

    Since you are a new user of this site, on this occasion, I have changed it for you.

    However, if you continue to use weak titles, you can expect to have your thread BLOCKED until you change it yourself. If that happens, to edit the thread title, open the original post, click "Edit Post" (bottom right) to access the area where you can edit your title.

    *Other search engines are available!!!

    Regards,

    Glenn.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Registered User
    Join Date
    02-01-2021
    Location
    United Kingdom
    MS-Off Ver
    1808
    Posts
    2

    Re: SUM values with criteria

    Quote Originally Posted by CARACALLA View Post
    Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.
    Done this for you

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: SUM values with criteria

    If I understand you correctly (there were no expected answers provided in your sample)...


    =SUMPRODUCT((TEXT($B$3:$B$14,"mmm")=G3)*($C$3:$C$14=1)*$D$3:$D$14)
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: SUM values with criteria

    H3=SUMPRODUCT((MONTH(Table2[DATE])=MONTH($G3&0))*(Table2[PERCENTAGE]=1)*(Table2[DATE]<>"")*(Table2[PAY]))

    copy down

+ 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. Replies: 1
    Last Post: 03-25-2020, 08:06 AM
  2. [SOLVED] Values based on multiple criteria including grouped criteria
    By adsako in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 03-21-2020, 01:24 PM
  3. Replies: 2
    Last Post: 01-03-2017, 08:40 AM
  4. Replies: 4
    Last Post: 11-01-2016, 03:28 PM
  5. VBA Macro Dividing Values Based upon Matching Criteria and Description Criteria
    By pvsvprasad in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 09-18-2016, 12:39 PM
  6. [SOLVED] How to sum values based on multiple criteria with multple possibilities for each criteria?
    By boredaxel in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-05-2013, 02:26 AM
  7. Replies: 2
    Last Post: 10-05-2011, 12:43 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