If you are using Excel 2007+, use COUNTIFS for count and SUMIFS for sum. If using an older Excel, use SUMPRODUCT for either.
IF(AND(data!F:F = "A"),and(data!B:B, = "04 - Closed Won"), and(data! D:D, = Oct-11),count, "0") =COUNTIFS(data!F:F,"A",data!B:B,"04 - Closed Won",data!D:D,DATE(2011,10,1)) I think does what you are trying to do... This is the equivalent SUMPRODUCT: =SUMPRODUCT(--(data!F:F="A",--(data!B:B="04 - Closed Won"),--(data! D:D=DATE(2011,10,1))) SUMPRODUCT is more versatile of the two, but also more cryptic when used for these purposes. COUNTIFS and SUMIFS are much faster. Read the help for COUNTIFS/SUMIFS if you have a newer Excel. By the way, your use of AND() is incorrect. Excel evaluates formulas from the inside out -- not the same as human language. Look for the most nested parenthesis and resolve that part of the formula in your mind first, then look at the next nesting level outside of that... repeat until you've resolved everything. If you realize that, then you'lll see that the "and" in AND(data!F:F = "A") won't do anything.. there is only one thing inside the parenthesis. AND works with a list or array of things and returns TRUE if all of them are true, otherwise false. -----Original Message----- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Jedi Spencee Sent: Tuesday, November 22, 2011 9:30 AM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Need Help with IF(AND formula Hello- I am trying to write a formula that would return the COUNT and SUM, with 3 criteria elements. IF(AND(data!F:F = "A"),and(data!B:B, = "04 - Closed Won"), and(data! D:D, = Oct-11),count, "0") Can this be done? Thank you. -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
