# 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!   Register To Reply

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!  Register To Reply

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  Register To Reply

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!  Register To Reply