Array formula with latest date condition?

[deacon10]

Hi all,

I am wondering if any of you excel genius can help me out with the formula below…..

=SUM((Sheet2!A$4:$A$17475=AM2918)*(Sheet2!B$4:$B$17475=AN2918)*(Sheet2!H$4:$H$17475="Left")*(Sheet2!$E$4:$E$17475>=AR2918)*(Sheet2!$E$4:$E$17475<=AV2918))

The above formula works fine and it calculates the sum of values in an array that match certain criteria. The problem I have is that I am trying to add an extra condition that says “only add the sum of items with the latest date”
Do any of you excel genius’s know how to do this, if the dates were found on Sheet2! $F$4:$F$17475?

The F column contains many varying dates and I am just trying to sum the largest?

Does anybody know how to do this because I have been struggling for quite some time?

Any help massively appreciated,

Cheers

 

[mancubus]

perhaps...

*(Sheet2!F$4:$F$17475=Max(Sheet2!F$4:$F$17475))

 

[deacon10]

Hi, thanks for your help it is very much appreciated.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Sadly the below formula doesn’t appear to work<o></o>
{=SUM((Sheet2!A$4:$A$17475=AM2918)*(Sheet2!B$4:$B$17475=AN2918)*(Sheet2!H$4:$H$17475="Left")*(Sheet2!$E$4:$E$17475>=AR2918)*(Sheet2!$E$4:$E$17475<=AV2918)*(Sheet2!F$4:$F$17475=Max(Sheet2!F$4:$F$17475)))}<o></o>
<o></o>
Whereas before without…..<o></o>
*(Sheet2!F$4:$F$17475=Max(Sheet2!F$4:$F$17475)<o></o>
<o></o>
….I would get a result of 26, but I now receive a value of 0 for all records.<o></o>
<o></o>
I have evaluated the formula and it now appears to return a value of ‘False’ for all records, whereas I would expect to see a number of ‘True’ values, but instead of the result being 26 It should be 3 (i.e. just counting the sum of items with the latest date).<o></o>
<o></o>
Does anybody have any ideas of where it is going wrong, or have any other formula suggestions?<o></o>
<o></o>
Big thanks,<o></o>

 

[Marcelo Branco]

Maybe

To simplify the formula lets use a helper cell, say A1, to get the MAX

Array-formula in A1 (untested)

=MAX(IF(Sheet2!A$4:$A$17475=AM2918,IF(Sheet2!B$4:$B$17475=AN2918,IF(Sheet2!H$4:$H$17475="Left",IF(Sheet2!$E$4:$E$17475>=AR2918,IF(Sheet2!$E$4:$E$17475<=AV2918,Sheet2!F$4:$F$17475))))))

Ctrl+Shift+Enter

Then add this condition to your original formula

*(Sheet2!$F$4:$F$1745=$A$1)

M.

 

[deacon10]

Hmmm, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thanks Marcelo I have done that and the plot thickens I now have the following formula which finds the latest date based on a number of conditions. This works fantastic, so thanks again to Marcelo for this.<o></o>
In cell E2917 I have this…..
<o></o>
=MAX(IF(sheet2!A$4:$A$17475=AN2917,IF(sheet2!B$4:$B$17475=AO2917,IF(sheet2!H$4:$H$17475="Left",IF(sheet2!$E$4:$E$17475>=AS2917,IF(sheet2!$E$4:$E$17475<=AW2917, sheet2!F$4:$F$17475))))))<o></o>
<o></o>
The above formula is in the adjacent cell to this…..<o></o>
In cell D2917…..
<o></o>
=SUM((sheet2!$A$4:$A$17475=AN2917)*(sheet2!$B$4:$B$17475=AO2917)*(sheet2!$H$4:$H$17475="Left")*(sheet2!$E$4:$E$17475>=AS2917)*(sheet2!$E$4:$E$17475<=AW2917)*(sheet2!$F$4:$F$1745=E2917))<o></o>
<o></o>
This formula should calculate the sum of items with the latest date, based on different criteria, but bizarrely I get the result #N/A.<o></o>
<o></o>
If I don’t put in the last portion of the formula *(Sheet2!$F$4:$F$1745=$E$2917) it works fine and calculates the sum. Unfortunately for me I need to calculate the sum of items with the latest date.<o></o>
<o></o>
Does anybody have any ideas where this is going wrong, both formulas have been stored as an array i.e ctrl + shift + enter and I have the date set to a standard date format in column F sheet2?

Many many hopeful thanks,<o></o>

CA

 

[Marcelo Branco]

Hi CA,

If the formula in E2917 is working fine - getting the MAX with exactly the same conditions - i cant understand why the SUM formula is not working...

Maybe someone else can help

Do you have blank cells in any column?

 

[Marcelo Branco]

Hey, you have a typo in your SUM formula

=SUM((sheet2!$A$4:$A$17475=AN2917)*(sheet2!$B$4:$B$17475=AO2917)*(sheet2!$H$4:$H$17475="Left")*(sheet2!$E$4:$E$17475>=AS2917)*(sheet2!$E$4:$E$17475<=AW2917)*(sheet2!$F$4:$F$1745=E2917))<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Try $F$17475

My fault, sorry (see #4)

 

[deacon10]

Ha,

Its amazing what I miss when looking at formulas for about 8 hours.

Well another success story for this amazingly helpful message board. I cant thank you enough Marcelo, this works an absolute treat!

Super big thanks to you sir,

CA

 

[Marcelo Branco]

Ha,

Its amazing what I miss when looking at formulas for about 8 hours.

Well another success story for this amazingly helpful message board. I cant thank you enough Marcelo, this works an absolute treat!

Super big thanks to you sir,

CA

You are welcome and tks for the feedback

M.