Solved

# Array Formula method

Posted on 2012-08-30
343 Views
I have a vector of 7 pay sources, a vector of 7 pay amounts and an array of pay dates across 7 columns and a long list of rows.
I have a calendar style range of cells with S M T W T F S  across the top and the calendar dates generated beneath.
I want the total pay for each calendar date in an adjacent range.

For example, he formula for one source is U74 =IF(ISNA(MATCH(L74,INDEX(Pay.Dates,,MATCH(INDEX(Pay.Source,1),Pay.Source,0)),0)),0,INDEX(Pay.Amount,MATCH(INDEX(Pay.Source,1),Pay.Source,0)))

For all 7 I tried CSE =sum(IF(ISNA(MATCH(L74,INDEX(Pay.Dates,,MATCH(INDEX(Pay.Source,{1,2,3,4,5,6,7}),Pay.Source,0)),0)),0,INDEX(Pay.Amount,MATCH(INDEX(Pay.Source, {1,2,3,4,5,6,7} ),Pay.Source,0))))
But this returns just Pay.source 1.
So I could have Sum(IF(ISNA(MATCH(L74,INDEX(Pay.Dates,,MATCH(INDEX(Pay.Source,1),Pay.Source,0)),0)),0,INDEX(Pay.Amount,MATCH(INDEX(Pay.Source,1),Pay.Source,0))). IF(ISNA(MATCH(L74,INDEX(Pay.Dates,,MATCH(INDEX(Pay.Source,2),Pay.Source,0)),0)),0,INDEX(Pay.Amount,MATCH(INDEX(Pay.Source,2),Pay.Source,0))), etc)
but this is buttugly.
And the number of sources could enlarge.
So how?
Regards
0
Question by:canesbr

LVL 85

Expert Comment

Any chance of a workbook? :)
0

LVL 24

Expert Comment

You could create a Function in VBA to retrive the data based upon the variables.
An example (with dummy data) will be very handy in answering this.

Have you tried a SUMIFS?
0

Author Comment

@The_Barman I think sumifs is 'and' and I need 'or'
Regards
Brian
Pay-Calendar.2.xlsm
0

LVL 50

Accepted Solution

A simple SUMPRODUCT should do it, i.e.

=SUMPRODUCT((Pay.Dates=L73)*Pay.Amount)

Just include that inside your original formula, i.e. this formula in U73 copied across and down

=TEXT(L73,"m/d/yyy")&CHAR(10)&TEXT(SUMPRODUCT((Pay.Dates=L73)*Pay.Amount),"#,#.00;;;")

regards, barry
0

Author Closing Comment

Magic.
I am supposing you used SumProduct to avoid array entering
=TEXT(L73,"m/d/yyy")&CHAR(10)&TEXT(SUM((Pay.Dates=L73)*Pay.Amount),"#,#.00;;;")
I guess I got too complexificated.
Thanks Barry
Regards
Brian
0

LVL 50

Expert Comment

Yes, SUMPRODUCT avoids array entry, so I prefer it personally but your version is just as good.....

regards, barry
0

Author Comment

I see my knowledge gap now - I didn't realize that you could mutiplify different dimensioned ranges.  Pay.Dates is 34x8 and Pay.Amount is  1x8
Hmm..
Regards
Brian
0

LVL 50

Expert Comment

Yes, that's right so essentially you get a 34*8 result, made up of zeroes mainly, where the dates don't match and the relevant values from Pay.amount where the dates match, then SUM or SUMPRODUCT sums that array.

Potentially the only downside would be if Pay.Amount contained text values (which doesn't seem to be the case in your setup), but you could accommodate that by an "array entered" version like this:

=SUM(IF(Pay.Dates=L73,Pay.Amount))

regards, barry
0

## Featured Post

### Suggested Solutions

I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.