+ Reply to Thread
Results 1 to 15 of 15

Auto-sum with multiple conditions

  1. 06-26-2022, 07:06 AM #1
    Thomas!
    Thomas! is offline
    Registered User
    Join Date
    06-26-2022
    Location
    Netherands
    MS-Off Ver
    Office 365
    Posts
    21

    Auto-sum with multiple conditions

    Good afternoon all,

    My question might be an easy one for a lot of you, but I'm struggling a lot to get it right.

    I try to make a formula which calculates the total amount with two conditions combined. The first condition is the subject in column A and the second condition is the month in column G. (see attached example file)

    For example I like to know how many bank costs we had in January by calculating all bank costs from January in column B (€) - the bank costs in column C + the bank costs in column D (PLN) (but calculated to € with an exchange rate, for example 4.6) - the bank costs in column E (PLN) (and also calculated to €).

    I tried different formulas, but non of them worked for me. I hope somebody has a good solution!

    Thank you in advance and have a nice day!


    Kind regards,

    Thomas
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 06-26-2022, 07:10 AM #2
    AliGW
    AliGW is online now
    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
    80,828

    Re: Auto-sum with multiple conditions

    Welcome to the forum.

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.
    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.
    Reply With QuoteRegister To Reply
  3. 06-26-2022, 07:20 AM #3
    Glenn Kennedy
    Glenn Kennedy is online now
    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
    44,050

    Re: Auto-sum with multiple conditions

    One way:

    =SUMPRODUCT((TEXT(Tabel1[Date:],"mmm")="Jan")*(Tabel1[Project/Cost:]="Bank Costs")*(Tabel1[Debit (€)]-Tabel1[Credit (€)]))+SUMPRODUCT((TEXT(Tabel1[Date:],"mmm")="Jan")*(Tabel1[Project/Cost:]="Bank Costs")*(Tabel1[Debit (PLN)]-Tabel1[Credit (PLN)]))/4.6

    You may need ; as the separator in NL.
    Attached Files Attached Files
    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
    Reply With QuoteRegister To Reply
  4. 06-27-2022, 03:56 AM #4
    Thomas!
    Thomas! is offline
    Registered User
    Join Date
    06-26-2022
    Location
    Netherands
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Auto-sum with multiple conditions

    Good morning Glenn,

    Thanks a lot! It works and you made my job a bit easier at the moment
    Have a nice day!


    Kind regards,

    Thomas
    Reply With QuoteRegister To Reply
  5. 06-27-2022, 04:28 AM #5
    Glenn Kennedy
    Glenn Kennedy is online now
    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
    44,050

    Re: Auto-sum with multiple conditions

    You're welcome.


    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  6. 04-12-2023, 08:19 AM #6
    Thomas!
    Thomas! is offline
    Registered User
    Join Date
    06-26-2022
    Location
    Netherands
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Auto-sum with multiple conditions

    Good afternoon Glenn,

    You helped me with this solution last year. But I like to ask you how I need to modify this sum when I like to refer to another tab. Let's say ''Tab1'' for example.
    I ask this question because I like to move the sums I made to another tab, but all the information for the sum will stay in the first tab.
    Can you help me with this?

    Hope to hear from you!


    Kind regards,

    Thomas
    Reply With QuoteRegister To Reply
  7. 04-12-2023, 11:37 AM #7
    Glenn Kennedy
    Glenn Kennedy is online now
    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
    44,050

    Re: Auto-sum with multiple conditions

    What happened when YOU copy/pasted the formula into another sheet?

    Try it yourself, now. Or look in the attached file. Or both.

    You're welcome.


    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 04-13-2023, 12:29 AM #8
    Czeslaw
    Czeslaw is offline
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,292

    Re: Auto-sum with multiple conditions

    Power Query
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 04-13-2023, 05:38 AM #9
    Thomas!
    Thomas! is offline
    Registered User
    Join Date
    06-26-2022
    Location
    Netherands
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Auto-sum with multiple conditions

    Good morning Glenn,

    Thank you for your answer.
    It works indeed like you told me. But I can't use the formula in the attached file. Tab 3 (Overview per quarter 2023) cel B3 & B4 for example.
    Do you know what I'm doing wrong?


    Thank you in advance!

    Thomas
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 04-13-2023, 06:13 AM #10
    Glenn Kennedy
    Glenn Kennedy is online now
    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
    44,050

    Re: Auto-sum with multiple conditions

    Check your source data (not the sample you uploaded).

    Sheet FA 2023, cell H73. Is it Blank or are there a few spaces in it. They are the cause of the problem.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 04-13-2023, 06:45 AM #11
    Thomas!
    Thomas! is offline
    Registered User
    Join Date
    06-26-2022
    Location
    Netherands
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Auto-sum with multiple conditions

    I did check this cell, and the ones right next to it. But they are all clear. How can I check source data?
    Reply With QuoteRegister To Reply
  12. 04-13-2023, 07:26 AM #12
    Glenn Kennedy
    Glenn Kennedy is online now
    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
    44,050

    Re: Auto-sum with multiple conditions

    Reopen your attachment at Post 9. Go to FA 2023, delete the content at H73. The formula now works perfectly.
    Reply With QuoteRegister To Reply
  13. 04-13-2023, 07:47 AM #13
    Thomas!
    Thomas! is offline
    Registered User
    Join Date
    06-26-2022
    Location
    Netherands
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Auto-sum with multiple conditions

    You are right, I deleted some rows in the other document so it was another cell with spaces. Thank you very much!
    Reply With QuoteRegister To Reply
  14. 04-13-2023, 07:50 AM #14
    Glenn Kennedy
    Glenn Kennedy is online now
    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
    44,050

    Re: Auto-sum with multiple conditions

    All good now?

    You're welcome.


    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  15. 04-13-2023, 09:05 AM #15
    Thomas!
    Thomas! is offline
    Registered User
    Join Date
    06-26-2022
    Location
    Netherands
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Auto-sum with multiple conditions

    Yes all good, thanks!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Using IF function with multiple conditions to auto populate cell
    By axangec in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-20-2021, 03:59 AM
  2. Auto Calculate based on the multiple conditions including date range
    By PunitA in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-25-2020, 12:57 PM
  3. [SOLVED] Help for IF conditions with multiple Conditions (scenario more than 5 conditions)
    By meily_o26 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-16-2019, 02:13 AM
  4. Auto Generating Data with Multiple Conditions
    By Siops in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-12-2016, 10:17 PM
  5. Retrieve data from table - multiple conditions / conditions within conditions
    By weedtm in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-14-2016, 03:53 PM
  6. [SOLVED] Vlookup - Country conditions (Multiple conditions)
    By dluhut in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-26-2013, 11:42 AM
  7. [SOLVED] auto fill ref with given conditions
    By slxia1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-03-2012, 10:43 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1