canesbr
asked on
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(P ay.Dates,, MATCH(INDE X(Pay.Sour ce,1),Pay. Source,0)) ,0)),0,IND EX(Pay.Amo unt,MATCH( INDEX(Pay. Source,1), Pay.Source ,0)))
For all 7 I tried CSE =sum(IF(ISNA(MATCH(L74,IND EX(Pay.Dat es,,MATCH( INDEX(Pay. Source,{1, 2,3,4,5,6, 7}),Pay.So urce,0)),0 )),0,INDEX (Pay.Amoun t,MATCH(IN DEX(Pay.So urce, {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,INDE X(Pay.Date s,,MATCH(I NDEX(Pay.S ource,1),P ay.Source, 0)),0)),0, INDEX(Pay. Amount,MAT CH(INDEX(P ay.Source, 1),Pay.Sou rce,0))). IF(ISNA(MATCH(L74,INDEX(Pa y.Dates,,M ATCH(INDEX (Pay.Sourc e,2),Pay.S ource,0)), 0)),0,INDE X(Pay.Amou nt,MATCH(I NDEX(Pay.S ource,2),P ay.Source, 0))), etc)
but this is buttugly.
And the number of sources could enlarge.
So how?
Please and thank you
Regards
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(P
For all 7 I tried CSE =sum(IF(ISNA(MATCH(L74,IND
But this returns just Pay.source 1.
So I could have Sum(IF(ISNA(MATCH(L74,INDE
but this is buttugly.
And the number of sources could enlarge.
So how?
Please and thank you
Regards
Any chance of a workbook? :)
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?
An example (with dummy data) will be very handy in answering this.
Have you tried a SUMIFS?
ASKER
@rorya Please see attached workbook.
@The_Barman I think sumifs is 'and' and I need 'or'
Regards
Brian
Pay-Calendar.2.xlsm
@The_Barman I think sumifs is 'and' and I need 'or'
Regards
Brian
Pay-Calendar.2.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Magic.
I am supposing you used SumProduct to avoid array entering
=TEXT(L73,"m/d/yyy")&CHAR( 10)&TEXT(S UM((Pay.Da tes=L73)*P ay.Amount) ,"#,#.00;; ;")
I guess I got too complexificated.
Thanks Barry
Regards
Brian
I am supposing you used SumProduct to avoid array entering
=TEXT(L73,"m/d/yyy")&CHAR(
I guess I got too complexificated.
Thanks Barry
Regards
Brian
Yes, SUMPRODUCT avoids array entry, so I prefer it personally but your version is just as good.....
regards, barry
regards, barry
ASKER
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
Hmm..
Regards
Brian
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
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.
regards, barry