Sumproduct a subarray in a separate closed worksheet

0
votes

I have two worksheets; I need to pull the "pounds shipped" for every day into worksheet two, which I obtain from worksheet one. The number is the sum of all the pounds shipped for all the orders that happen every day. I used this formula to get the correct answer:

=SUMIF('[Worksheet One]'!$C:$C, A2, '[Worksheet One]'!$AA$AA)

But because it's sumif and not sumproduct it won't update when worksheet one is closed. Column C is the date, A2 is the date on worksheet two, and column AA is the pounds shipped.

I've tried using a SUMPRODUCT( INDEX( SMALL( IF( ROW() - ROW(), ROWS())))) formula but either it doesn't work or I couldn't get the right format.

Any ideas? Thanks

1
excel

1 Answers

1
votes

to translate a SUMIF or SUMIFS to a SUMPRODUCT formula, you have to use --() around the comparison test. That will convert TRUE to 1 and FALSE to 0

So, from your formula of 

=SUMIF('[Worksheet One]'!$C:$C, A2, '[Worksheet One]'!$AA$AA)

we would end up with

=SUMPRODUCT(--('[Worksheet One]'!$C:$C = A2), '[Worksheet One]'!$AA$AA)

another example with 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))