excel - Sumproduct a subarray in a separate closed worksheet -

excel - Sumproduct a subarray in a separate closed worksheet -

i have 2 worksheets; need pull "pounds shipped" every day worksheet two, obtain worksheet one. number sum of pounds shipped orders happen every day. used formula right answer:

=sumif('[worksheet one]'!$c:$c, a2, '[worksheet one]'!$aa$aa)

but because it's sumif , not sumproduct won't update when worksheet 1 closed. column c date, a2 date on worksheet two, , column aa pounds shipped.

i've tried using sumproduct( index( small( if( row() - row(), rows())))) formula either doesn't work or couldn't right format.

any ideas? thanks

to translate sumif or sumifs sumproduct formula, have utilize --() around comparing test. convert true 1 , false 0

so, formula of

=sumif('[worksheet one]'!$c:$c, a2, '[worksheet one]'!$aa$aa)

we end with

=sumproduct(--('[worksheet one]'!$c:$c = a2), '[worksheet one]'!$aa$aa)

another illustration sumifs,

=sumifs('[wrksht1]'!$aa$aa,'[wrksht1]'!$c:$c, a2,'[wrksht1]'!$q:$q, b2)

would become

=sumifs('[wrksht1]'!$aa$aa,--('[wrksht1]'!$c:$c=a2),--('[wrksht1]'!$q:$q=b2))

excel