• Status: Solved
• Priority: Medium
• Security: Public
• Views: 418

# Array Formula method

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?
Please and thank you
Regards
0
canesbr
1 Solution

Commented:
Any chance of a workbook? :)
0

Commented:
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 Commented:
@rorya Please see attached workbook.
@The_Barman I think sumifs is 'and' and I need 'or'
Regards
Brian
Pay-Calendar.2.xlsm
0

Commented:
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 Commented:
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

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

regards, barry
0

Author Commented:
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

Commented:
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

Tackle projects and never again get stuck behind a technical roadblock.