Forum Discussion
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))
I have Googled this issue but cannot find the right answer!
Any help would be great 🙂
Thanks
Simon
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)
- Try this, the cell A1 is where you have the function =TODAY()
=TEXT(DATE(YEAR($A$1),MONTH($A$1)-1,1),"MMYYY")- 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?
