+ Reply to Thread
Results 1 to 4 of 4

Multiple Conditon Formula for Monthly Spend

  1. #1
    Registered User
    Join Date
    06-15-2018
    Location
    Ldn
    MS-Off Ver
    2016
    Posts
    10

    Question Multiple Conditon Formula for Monthly Spend

    Hi!

    I'm wondering if someone could help me with a formula on my spreadsheet. I'm looking to calculate a monthly total spend (preferably also making sure that only the current year is calculated). However, this would be based on 3 columns:
    1. Date - for obvious reasons
    2. Price - the amount in $
    3. Paid? - if it has been paid

    If the paid column states N or N/A, then I do not want it to be included in the total month spend. Only when it is changed to Y should it be calculated in the total month spend column. I'm also looking to calculate a rough estimate of the conversion to 's in the next column.

    Hopefully this makes sense - I've attached an example of my spreadsheet.

    Greatful for anyone that can help me!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    04-19-2015
    Location
    pakistan
    MS-Off Ver
    2013
    Posts
    335

    Re: Multiple Conditon Formula for Monthly Spend

    try this at N4>> =SUMPRODUCT((TEXT($B$3:$B$5,"mmm")=M4)*($H$3:$H$5="Y"),$G$3:$G$5)

    i don't understand second part!
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    775

    Re: Multiple Conditon Formula for Monthly Spend

    N4=SUMPRODUCT((MONTH($B$3:$B$100)=MONTH($M4&0))*($H$3:$H$100="Y")*($B$3:$B$100<>"")*($G$3:$G$100))

    Copy down

  4. #4
    Registered User
    Join Date
    06-15-2018
    Location
    Ldn
    MS-Off Ver
    2016
    Posts
    10

    Re: Multiple Conditon Formula for Monthly Spend

    Thanks both! They both worked perfectly. I just used the formula =N4*Sheet5!$E$2 in the column to convert the exchange rate and on Sheet 5 cell E2 i just wrote the current conversion rate!

+ 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: 4
    Last Post: 05-04-2020, 06:33 AM
  2. [SOLVED] Multiple If conditions (4 If conditon) in a formula
    By hecgroups in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-29-2016, 10:02 AM
  3. How to calculate average monthly spend from unpredictable dates?
    By edwg in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-11-2015, 09:46 AM
  4. [SOLVED] Print multiple range from one sheet with conditon
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-01-2014, 12:04 AM
  5. Copy Down sum formula with conditon
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-26-2014, 10:16 AM
  6. Replies: 2
    Last Post: 10-30-2013, 09:50 PM
  7. How do I estimate the year spend if spend is $26000 as of May?
    By Felicia Pickett in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-19-2006, 11:15 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