Forum Discussion
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 beyond my very basic normal use of excel!
If column A is a list of dates(in a set format), column B is a list of names (from a drop-down list) and column C is a list of monetary values. Is it possible to use one of the ‘IF’ functions to recognise which dates in column A are less than 1 year ago from today, then of those matches, which relate to particular name from the drop-down list in column B, and from those matching both criteria then total the relevant monetary amounts from column C?
I keep getting tangled up in the syntax from the formula builder but I'm hoping its possible to do?
Many thanks
Jay
5 Replies
Hi JMc21,
This is possible, please attached the sample file with your desired results.
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
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
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
Really appreciate you responding but I can't see an attached file that you referred to!
Regards
Jay
I've managed to attach a file - 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.
Many thanks
Jay
