# Array formula with latest date condition?

#### deacon10

##### Board Regular
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

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
perhaps...
Code:
``*(Sheet2!F\$4:\$F\$17475=Max(Sheet2!F\$4:\$F\$17475))``

Hi, thanks for your help it is very much appreciated.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com ffice ffice" /><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 >

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

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

M.

Hmmm, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com ffice ffice" /><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

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?

Last edited:
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-com ffice ffice" /><o ></o >

Try \$F\$17475

My fault, sorry (see #4)

Last edited:
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

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.

Replies
15
Views
384
Replies
2
Views
263
Replies
5
Views
246
Replies
7
Views
603
Replies
3
Views
116

### Forum statistics

1,203,752
Messages
6,057,152
Members
444,908
Latest member
Jayrey ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back