Multiple Conditon Formula for Monthly Spend

1. 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!

2. 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!

3. 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. 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!

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

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