Quote:
Originally Posted by JulieB
Thanks SteveWcg and Pecoflyer for the quick reply. I was wondering why both of you gave me the sum of 2 quarters AR numbers. Apparently the summary data given is incorrect. The fiscal period is arranged in quarterly manner.
|
You should be able to use:
Code:
=SUMPRODUCT((Revenue!A:A=A2)*(Revenue!C:C=C2)*(Revenue!D:D=D2),Revenue!E:E)
or, if you want to be able to copy the formula to other columns as well:
Code:
=SUMPRODUCT((Revenue!$A:$A=Summary!$A2)*(Revenue!$C:$C=Summary!$C2)*(Revenue!$D:$D=Summary!$D2),Revenue!E:E)
in Summary!G2 and copy down as far as needed. However, you have a data entry error in Summary!C2, which has a space after 'FY12 Q1', which is why Pecoflyer had to add the TRIM statements. This highlights the crucial issue that data entry errors can invalidate the results. As they used to say:
garbage in : garbage out.