IF statement, AND & Vlookup on 3 condition that's not in numeric

macropod · #8 01-09-2014, 10:33 PM

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro - replace with condition	ubns	Word VBA	1	05-02-2012 12:52 AM
check with condition	karti	Word	2	03-15-2011 06:06 AM
Formula in a Cell with condition?	Learner7	Excel	1	07-19-2010 10:10 AM
Vlookup and If statement problem	bunnygum	Excel	1	03-24-2009 05:10 AM
Retrict Method Condition	markp	Outlook	0	12-22-2005 05:38 PM