Forum Discussion

JMc21's avatar
JMc21
Copper Contributor
May 19, 2020

Excel formula help

Hi I’m hoping someone might be able to tell me whether its possible to create a formula that I’m looking for.  I’ve been trying all sorts of Countif and countifs but with no luck and its probably bey...
office 365
JMc21's avatar
JMc21
Copper Contributor
May 27, 2020

Hi ExcelExciting 

 

I'm sorry I don't think I tagged you correctly when I replied previously.  I've now attached an example of what I'm trying to do - I hope it makes sense from what I've described in the first message.  I'm looking for the formula to calculate how many of the payments were cash in the last 12 months from today's date but unsure of how to do it.

 

Thanks again

 

Jay

ExcelExciting's avatar
ExcelExciting
MVP
Jun 06, 2020

Hi JMc21,

 

If you have Office365 then formula is little easy to construct because you have the new Dynamic Arrays. I have worked out on both the solution pls find attached the same

 

Solution 

 

 

 

Dynamic Arrays (Only Office 365 Users & Excel Online)

=FILTER($A$4:$C$24,($B$4:$B$24="cash")*(($A$4:$A$24)>=(EOMONTH(EDATE(MAX($A$4:$A$24),-12),-1)+1)),"")

 

Classic Arrays

=IFERROR(INDEX($A$4:$C$24,SMALL(IF((($B$4:$B$24="cash")*(($A$4:$A$24)>=(EOMONTH(EDATE(MAX($A$4:$A$24),-12),-1)+1)))=1,ROW($A$4:$A$24)-3,""),ROW($J$6:$J$14)-5),{1,2,3},1),"")

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer to help the other members find it more

 

Resources