+ Reply to Thread
Results 1 to 4 of 4

Sum values based on multiple criteria

  1. #1
    Registered User
    Join Date
    11-22-2020
    Location
    India
    MS-Off Ver
    2010
    Posts
    22

    Sum values based on multiple criteria

    I am having my daily transactions as + and - values in single column.
    Want to find out the iNcomes and Expenses by month and by payment mode.
    Please have a look on the attached file.
    Attached Files Attached Files
    Last edited by itsjamaal; 02-17-2022 at 03:39 AM. Reason: Solved by AliGW :)

  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,335

    Re: Sum values based on multiple criteria

    In K6 copied across and down:

    =SUMPRODUCT((MONTH(A$2:A$38=COLUMNS($K1:K1))*(F$2:F$38>0)*(D$2:D$38=$J6)*F$2:F$38))

    In K14 copied across and down:

    =SUMPRODUCT((MONTH(A$2:A$38=COLUMNS($K1:K1))*(F$2:F$38<0)*(D$2:D$38=$J6)*F$2:F$38))
    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
    Registered User
    Join Date
    11-22-2020
    Location
    India
    MS-Off Ver
    2010
    Posts
    22

    Re: Sum values based on multiple criteria

    Thanks for the quickest reply.
    I have edited for my convenient as

    =SUMPRODUCT((MONTH(DailyDates)=MONTH(K$3))*(Amounts>0)*(PayModes=$J6)*Amounts)

  4. #4
    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,335

    Re: Sum values based on multiple criteria

    No problem.

+ 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] If multiple criteria are met, then .. for all values based on 1 criteria
    By Amadan90 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-18-2021, 03:18 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: 4
    Last Post: 05-19-2015, 11:53 PM
  4. [SOLVED] using SUMIFS and adding values based on multiple criteria based on selection from the drop
    By muheebrahman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-25-2015, 12:55 AM
  5. [SOLVED] How to return multiple values to a single cell based on multiple criteria
    By lwallace in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-20-2014, 06:32 AM
  6. [SOLVED] Lookup and sum multiple values based on a single criteria in multiple sheets.
    By paulsanett in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2014, 12:18 PM
  7. [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

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