 SOLVED

# Excel formula query

Good afternoon,

I'm sure someone can assist me with this simple formula query

I have a spreadsheet which has multiple formula's including the one below which pulls data for the current month and year. How can i change this formula to show the previous month of the current year.

=SUM((TEXT(Bets!A2:A999,"mmyyyy")=TEXT(TODAY(),"mmyyyy"))*IF(ISNUMBER(Bets!P2:P999),Bets!P2:P999,0))

Any help would be great

Thanks

Simon

8 Replies

# Re: Excel formula query

Try this, the cell A1 is where you have the function =TODAY()
=TEXT(DATE(YEAR(\$A\$1),MONTH(\$A\$1)-1,1),"MMYYY")

# Re: Excel formula query

Previous month for the date could be taken from

``=EDATE( <date>, -1)``

# Re: Excel formula query

I have adjusted my sum which now looks like this...=SUM((TEXT(Bets!A2:A999,"mmyyyy")=TEXT(DATE(YEAR(\$A\$1),MONTH(\$A\$1)-1,1),"mmyyyy")*IF(ISNUMBER(Bets!P2:P999),Bets!P2:P999,0)))

All i get in #NUM in the cell. What am i doing wrong?

Thanks
Simon

# Re: Excel formula query

Should you not be using the sumifs?

# Re: Excel formula query

Are you trying to return the sum of the data of column P if the date in column A is equal to the Text formula?

# Re: Excel formula query

Hi. I'm trying to sum column P if column A matches the criteria set out in the sum. So if the date is last month then add the values in column P. Column A is set to Date field.
Happy to use SUMIFS, just not entirely sure how to!
Thanks
best response confirmed by SimonUK1753 (New Contributor)
Solution

# Re: Excel formula query

Hope this helps.

On your Bets tab, insert a new column next to the date column A. In this column put the formula

=TEXT(A3,"mmyyyy") On the tab you want to have the SUMIFS formula: A1 =TODAY()

A4 =TEXT(DATE(YEAR(\$A\$1),MONTH(\$A\$1),1),"MMYYY")

B4  =SUMIFS(Bets!\$P:\$P,Bets!\$B:\$B,A4)

A7 =TEXT(DATE(YEAR(\$A\$1),MONTH(\$A\$1)-1,1),"MMYYY")

B7 =SUMIFS(Bets!\$P:\$P,Bets!\$B:\$B,A7)

# Re: Excel formula query

@JSHEP73 Thank you for your help!

Your solution has worked and given me what i require.

Thanks again.