SOLVED

Excel formula query

%3CLINGO-SUB%20id%3D%22lingo-sub-2908716%22%20slang%3D%22en-US%22%3EExcel%20formula%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2908716%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20afternoon%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20sure%20someone%20can%20assist%20me%20with%20this%20simple%20formula%20query%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20which%20has%20multiple%20formula's%20including%20the%20one%20below%20which%20pulls%20data%20for%20the%20current%20month%20and%20year.%20How%20can%20i%20change%20this%20formula%20to%20show%20the%20previous%20month%20of%20the%20current%20year.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM((TEXT(Bets!A2%3AA999%2C%22mmyyyy%22)%3DTEXT(TODAY()%2C%22mmyyyy%22))*IF(ISNUMBER(Bets!P2%3AP999)%2CBets!P2%3AP999%2C0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20Googled%20this%20issue%20but%20cannot%20find%20the%20right%20answer!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20great%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ESimon%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2908716%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2908819%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2908819%22%20slang%3D%22en-US%22%3ETry%20this%2C%20the%20cell%20A1%20is%20where%20you%20have%20the%20function%20%3DTODAY()%3CBR%20%2F%3E%3DTEXT(DATE(YEAR(%24A%241)%2CMONTH(%24A%241)-1%2C1)%2C%22MMYYY%22)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2909283%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2909283%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1202213%22%20target%3D%22_blank%22%3E%40SimonUK1753%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPrevious%20month%20for%20the%20date%20could%20be%20taken%20from%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DEDATE(%20%3CDATE%3E%2C%20-1)%3C%2FDATE%3E%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2914048%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2914048%22%20slang%3D%22en-US%22%3EThanks%20for%20your%20reply.%3CBR%20%2F%3EI%20have%20adjusted%20my%20sum%20which%20now%20looks%20like%20this...%3DSUM((TEXT(Bets!A2%3AA999%2C%22mmyyyy%22)%3DTEXT(DATE(YEAR(%24A%241)%2CMONTH(%24A%241)-1%2C1)%2C%22mmyyyy%22)*IF(ISNUMBER(Bets!P2%3AP999)%2CBets!P2%3AP999%2C0)))%3CBR%20%2F%3E%3CBR%20%2F%3EAll%20i%20get%20in%20%23NUM%20in%20the%20cell.%20What%20am%20i%20doing%20wrong%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%3CBR%20%2F%3ESimon%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New 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 (New 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.