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

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
Asked:
canesbr
1 Solution
 
Rory ArchibaldCommented:
Any chance of a workbook? :)
0
 
SteveCommented:
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
 
canesbrAuthor Commented:
@rorya Please see attached workbook.
@The_Barman I think sumifs is 'and' and I need 'or'
Regards
Brian
Pay-Calendar.2.xlsm
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
barry houdiniCommented:
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
 
canesbrAuthor 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
 
barry houdiniCommented:
Yes, SUMPRODUCT avoids array entry, so I prefer it personally but your version is just as good.....

regards, barry
0
 
canesbrAuthor 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
 
barry houdiniCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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