INDEX MATCH inside of SUMSIF?

Can someone please take a look at this sample doc. I put all the information on one sheet in this example but the 3 tables are separate worksheets. I'm trying to generate the numbers that are in yellow from the other two tables. I'm trying to find a way to use the raw date from sheet 1 and have the criteria range know that I'm talking about a specific week number.


The only thing I can think of (other than a macro) that might work is maybe for the criteria range2, putting in an index match so that it somehow knows how to convert the raw date into the correlating week number.


This is what I have so far: the ??? represents the criteria range2 where I'm supposed to put the dates from sheet 1.

=SUMIFS(C3:C10,A3:A10,A14,?????,B13)
=SUMIFS(C3:C10,A3:A10,A14,INDEX(B3:B10,MATCH(B13,G4:G11,0),MATCH(A14,A3:A10,0)),B13)

limitations:

I don't want to manipulate Sheet 1 (add any columns).
I don't want to use a macro unless this isn't possible via formulas.


Is there something wrong with my formula or Is there a better way to do this.

Any thoughts are appreciated.

Thank you greatly for responding and helping me to know how to make things clearer.

I corrected the table for Tom and Joe. I'm sorry about those typos.

-Jack is 5 under W1 because he is listed in Sheet 1 with the date 6/15/2016. This date correlates with W1 from sheet 2 (a fixed calendar). That is the only entry for him so the other weeks are 0.
-Mary is 13 under W1 because she is listed with two dates. Both those dates correlate to W1 so the values 9 and 4 are added.
-Joe also has two entires in sheet 1, both with different dates that correlate with a different week. He has 8 under W1 and 3 in W2.

BTW I was using sumsif because if a name occurs more than once for the same week, those values from sheet 1 are added.

I'm going to use a helper column in sheet 1 because it isn't possible without it, far as I know...(without VBA anyway) You can simply right click the column and hide it.



I used:

=A2&"_"&INDEX(Sheet2!$B:$B,MATCH(Sheet1!B2,Sheet2!$A:$A,0))

for the helper column, all it does is tell which week the respective name is.

Then I populated the table with this formula:

=SUMIF(Sheet1!$D$2:$D$9,Sheet3!$A2&"_"&Sheet3!B$1,Sheet1!$C$2:$C$9)

I also used unique name ranges and stuff to make that part easier for you also. You can see the name ranges by clicking formulas at the top, then going to name manager. I created one called Name and UniqueName. Few formulas and stuff that make them possible. Most notably, the array entered formula:

=INDEX(Name,MATCH(0,COUNTIF($A$1:A1,Name),0))

which you can see in the unique name column on sheet3.

Workbook attached, let me know if any questions, hopefully the helper column doesn't make the workbook irrelevant...

Another way ... array-entered in B14 filled down and across

Formula:

=SUM((INDEX($F$4:$F$11,N(IF(1,MATCH(1,1/(B$13=$G$4:$G$11),{0,1}))))=$B$3:$B$10)*($A14=$A$3:$A$10)*$C$3:$C$10)

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

There were a trailing space in the names A14:A18. I corrected it in the attached.

If this can be done with SUMIFS I do not see it.

I hope to try to decipher what everything means [(1) the N, 2) How/why you made the IF test and lookup value to be 1, and 3)the usage of / (this isn't really divide is it?)]

I'll attempt to take these one at a time. The N(IF(1, is an array coercion technique. Occasionally (and fortunately it's rare) an array of values will stubbornly refuse to pass on

anything except the first element of the array. The N(IF(1, strategy forces the whole array to be passed. It all relies upon secondary functions of N and IF. The IF(1, is thought of as a

tautology. The 1 forces a TRUE. There are other techniques as well. If you would like to know more about these you might find this site of interest.

https://excelxor.com/category/coerci...ay-of-returns/

If it does not take you directly to the article click on "Coercing an array of returns".

The 1/ inside the MATCH function returns an array of {1;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!} 1s and #DIV/0! errors. MATCH has the fortunate property of ignoring

errors in its second argument. The "magic" in this formula is in the 3rd argument of MATCH [match_type]. Ordinarily we use only one value. In this case MATCH can be instructed to

perform 'double-duty' with both {0,1} match types. Match will simultaneously return the first exact matching 1 in the above array and the last 1 in the same array. If you were to select

any of the formula cells, select only the above MATCH function in the formula bar and press the F9 function key you would see {1,2} under W1, {3,4} under W2, {5,6} under W3 etc.

And by-the-way, this formula works like that only because there are no more than two W# in G4:G10. If there were more we would need a different and probably more complicated

formula.

I like how it automatically fills in the data when I add an entry to the yellow area. ....... can you please share how you accomplished that?

That is more or less automatic. Since the formula is referencing absolute ranges while referencing row relative names in A14:A?? and column relative W# in row 13 that part solves itself. Referring to the comment in the last upload grabbing the fill handle and dragging down and then across takes care of this.

Also, Is there a way to make the columns generic? That is, removing the absolute cell references so that the formula will recognize the entire list if an entry is added to sheet 1 or 2 also? I tried to remove the $'s and make then for example A:A but then I get #NAME error.

If you need flexibility in those absolute ranges I would suggest making the references a little bit larger than anticipated need. I.E. if you anticipate a maximum range of 200 rows make

all of the ranges say 250 rows. The main thing you want to avoid is referencing whole columns / rows .... that forces Excel to do math operations on over 1 million rows in

each copy of the formula. It slows the workbook down. Added to that is the fact that this is an array formula. Array formulas are resource hungry. The whole column / row reference

magnifies the dilemma.

Has this helped?

Questionz,

Try this much simpler formula. It does not have to be array-entered.

Formula:

=SUMPRODUCT(SUMIFS($C$3:$C$12,$B$3:$B$12,$F$4:$F$11,$A$3:$A$12,$A16),COUNTIF(B$15,$G$4:$G$11))

It is far more flexible than my former.

That way of doing COUNTIF breaks down the distribution one-by-one of the counts. Select it, press F9 function key .... {1;1;0;0;0;0;0;0}

I that was a "left-over" from trying to reconcile rows in the SUMIFS during formula build. I forgot that --(B$15=$G$4:$G$11) is simpler and does just as well. F9ed {1;1;0;0;0;0;0;0}.

That other formula would be:

=SUMPRODUCT(SUMIFS($C$3:$C$13,$B$3:$B$13,$F$4:$F$11,$A$3:$A$13,$A16),--(B$15=$G$4:$G$11))

Sorry for the confusion.