Re: $$Excel-Macros$$ Need Help with IF(AND formula
Thank you! On Tue, Nov 22, 2011 at 10:30 AM, Jedi Spencee jedi.spen...@gmail.comwrote: 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 -- Erin -- 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
Re: $$Excel-Macros$$ Need Help with IF(AND formula
Hi, If you are using Excel 2007 or 2010, then you can use the SUMIFS or COUNTIFS to achieve the same. Cheers Andy On Tue, Nov 22, 2011 at 11:00 PM, Jedi Spencee jedi.spen...@gmail.comwrote: 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 -- Warm Regards, Aindril De Ph: 9811300157 -- 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
Re: $$Excel-Macros$$ Need Help with IF(AND formula
You can also try... =SUMPRODUCT((data!F:F = A)*(data!B:B=04 - Closed Won)*(data!D:D=Oct-11)*(data!K:K=0)) -- Thanks regards, Noorain Ansari *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ *http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/ On Tue, Nov 22, 2011 at 11:00 PM, Jedi Spencee jedi.spen...@gmail.comwrote: 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 -- 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
RE: $$Excel-Macros$$ Need Help with IF(AND formula
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
RE: $$Excel-Macros$$ Need Help with IF(AND formula
P.S. Oh - for taking the sum with sumproduct, just add the sum range as an additional argument to SUMPRODUCT (the order of arguments doesn't matter -- they are all multiplied by each other). If your values to sum are in column X: =SUMPRODUCT(--(data!F:F=A,--(data!B:B=04 - Closed Won),--(data! D:D=DATE(2011,10,1)),data!X:X) For SUMIFS, the range to sum is the first argument, so just use: =SUMIFS(data!X:X,data!F:F,A,data!B:B,04 - Closed Won,data!D:D,DATE(2011,10,1)) Note that it is more efficient to specify the actual data range, not the whole columns as you have. For more on the SUMPRODUCT usage described, check out these links: http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/ http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/ COUNTIFS and SUMIFS are still pretty versatile, and fast, so check the help for more info on how the arguments work. Asa -Original Message- From: Asa Rossoff [mailto:a...@lovetour.info] Sent: Tuesday, November 22, 2011 7:38 PM To: 'excel-macros@googlegroups.com' Subject: RE: $$Excel-Macros$$ Need Help with IF(AND formula 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
Re: $$Excel-Macros$$ Need Help for Macro or Formula
Thanks Harry i want formula for Column D. the formula what i have mention in email needs to be enhance or change 1. if for code in column A, status in column C is Paid,Not Paid,Awaiting then Final Status should be Not to process in column D 2. if for code in column A, status in column C is only paid then findal status should be process in column D 3. there is one blank row after each unique code in column A i hope i have clear my query. On Sat, May 15, 2010 at 10:28 AM, harpreetgujral harpreetguj...@gmail.comwrote: Hi Mahesh, You wrote: *Dear All * *for attach sheet i need formula or macro, I will appreciate any help on this. 1.if for code in column A ,status in column C is paid then D column status should be process 2.if status in column C is not paid or awaiting then D column status should be not to process 3. there is blank line between two codes in column A below mention formula not working. =IF(A3=,,IF(C3=Paid,Process,Not to process)) * In the attached sheet, first two points have been closed. 3) point is not clear at all. Syntax of the If formula is as follows: =IF(Condition,Statement if condition is true,statement if condition is false) If you can throw some more light on what do you want to achieve, then may be we can help you Thanks Harry -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Need Help for Macro or Formula
Enter Following Formula in Cell D2 =IF(C2=Paid,Process,IF(C2=,,(Not to Process))) Regards Jagjeet SIngh On May 14, 8:55 pm, Mahesh parab mahes...@gmail.com wrote: Dear All for attach sheet i need formula or macro, I will appreciate any help on this. 1.if for code in column A ,status in column C is paid then D column status should be process 2.if status in column C is not paid or awaiting then D column status should be not to process 3. there is blank line between two codes in column A below mention formula not working. =IF(A3=,,IF(C3=Paid,Process,Not to process)) -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links :http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group athttp://groups.google.com/group/excel-macros/subscribe Sample sheet.xls 26KViewDownload -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Need Help for Macro or Formula
check the sheet. On Fri, May 14, 2010 at 9:25 PM, Mahesh parab mahes...@gmail.com wrote: Dear All for attach sheet i need formula or macro, I will appreciate any help on this. 1.if for code in column A ,status in column C is paid then D column status should be process 2.if status in column C is not paid or awaiting then D column status should be not to process 3. there is blank line between two codes in column A below mention formula not working. =IF(A3=,,IF(C3=Paid,Process,Not to process)) -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- Thanks Saurabh Singh -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe Sample sheet-updtd.xls Description: MS-Excel spreadsheet
