SOLVED

Excel formula query

Copper Contributor

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))

 

I have Googled this issue but cannot find the right answer!

 

Any help would be great :)

 

Thanks

Simon

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

@SimonUK1753 

Previous month for the date could be taken from

=EDATE( <date>, -1)
Thanks for your reply.
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


Should you not be using the sumifs?
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?
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 (Copper Contributor)
Solution

@SimonUK1753 

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")

JSHEP73_0-1635947768835.png

On the tab you want to have the SUMIFS formula:

JSHEP73_1-1635947872210.png

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)

 

@JSHEP73 Thank you for your help!

Your solution has worked and given me what i require.

Thanks again. :)
1 best response

Accepted Solutions
best response confirmed by SimonUK1753 (Copper Contributor)
Solution

@SimonUK1753 

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")

JSHEP73_0-1635947768835.png

On the tab you want to have the SUMIFS formula:

JSHEP73_1-1635947872210.png

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)

 

View solution in original post