Excel formula help

[JMc21]

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

[#2]

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

[#3]

Hi @Faraz Shaikh 

Really appreciate you responding but I can't see an attached file that you referred to!

Regards

Jay

[#4]

Hi @Faraz Shaikh

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

[#5]

Hi @Faraz Shaikh 

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

[#6]

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