Array Formula required

[surfman68]

Hi guys

I have attached a spreadsheet of data that I would like to reduce.

In the workbook there is a set of data (rows 1 - 24) which are defined with random dates (row 2) and number criteria column A.

In the table below that (rows 28 to 37) I have set out how I would like to present the data.

So the question is; how to create an array formula that sums the above data months into the criteria of column A.

if there is any help out there please point me in the right direction

Cheers

[Ron Coderre]

It seems like a regular formula will work.
Using your posted workbook, put this formula in cell B29 and copy it across and down as far as you need

=IFERROR(INDEX($B$3:$FG$23,MATCH($A29,$A$3:$A$23,0),MATCH(B$28,$B$2:$FG$2,0)),"")

Is that something you can work with?

[surfman68]

Hi Ron

Thanks for the reply,

When copied as you mentioned, close - I get a few cells filled out but this is not quite what I was after,

I should explain a bit better;

The function you provided identifies date matches from data table (B3-FG23) but I would actually like to cell B29 to sum all of the rows with 490 (A29) but the sum constrained to the month ending the date in B28.

So for example that in cell C29 it will sum rows corresponding to A29 but between the dates 31/06/2011 and 30/07/2011 - etc

I have recopied the spread sheet with a manual example of what I hope to acheive automatically. The real data I am using is quite extensive and doing it manually for all would incur significant human error

I have tried sumif functions with a vlookup imbedded but i have not had it working yet

let me know what you think

cheers

[Ron Coderre]

I notice that those aren't true month-end dates.

Assuming you want to use fiscal-month-end dates
try this regular formula copied across and down

B29: =SUMPRODUCT(($A$3:$A$23=$A29)*(TEXT($B$2:$FG$2,"YYYYMM")=TEXT(B$28,"YYYYMM"))*(DAY($B$2:$FG$2)<=DAY(B$28))*$B$3:$FG$23)

or maybe this:

B29: =SUMPRODUCT(($A$3:$A$23=$A29)*($B$2:$FG$2>=(EOMONTH(B$28,-1)+1))*($B$2:$FG$2<=(B$28))*$B$3:$FG$23)

If you meant to use true month-end dates, try this:

B29: =SUMPRODUCT(($A$3:$A$23=$A29)*(TEXT($B$2:$FG$2,"YYYYMM")=TEXT(B$28,"YYYYMM"))*$B$3:$FG$23)

Does that help?

I have concerns that true end-of-month dates in the source data will be omitted, though. How do you want those handled?

[surfman68]

Hi Ron

Yes that does the trick, thank you.

It is so much more elegant than the options I was fiddling with.

I will probably work to the EOM but initially I steered away from this because of how the data is used elsewhere.

thanks again

[Saarang84]

Hi Surfman,

Your table layout seems to be poor for coding a SUMIFS formula, because, your data range is inconsistent. By inconsistent i mean the date format used, as I find just dates for the first 133 columns (from B to ED) correct and after that its a timestamp which is converted to d/mm/yyyy format. The Bench RL values aren't unique and are repeating as well (I'm referring to the values 490, 475, 460, etc. here).

Please do rework on the layout of your data table.