Re: $$Excel-Macros$$ Two columns and three criteria
Hi, Just try it.. =SUMPRODUCT(--($A$1:A10=A1)--($A$1:A10=A2)*(B1:B10=B1)) On Thu, Sep 15, 2011 at 9:09 AM, ArtySin kenstrain...@gmail.com wrote: Hi I have two columns as below using Excel 2000 (unfortunately, corporate no spend policy): These columns have been exported from a database and appear as the example below. A B Status Date Run Passed 22/08/2011 Failed 22/08/2011 Failed 23/08/2011 Passed 22/08/2011 Passed 23/08/2011 N/A 22/08/2011 N/A 22/08/2011 Passed 22/08/2011 N/A 23/08/2011 Failed 23/08/2011 What I want to do is add up the number of times either passed or failed appears against a date but do not want to include the N/A. For example to count up the total of passed or failed for the 22/08/2011 would give a result of 4 I've tried =SUMPRODUCT(--($A$2:$A$11=A2)--($A$2:$A$11=A3)*--($B$2:$B $11=B2)) and various countifs with AND statements but I can't get this to work. Many thanks ArtySin -- -- 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Two columns and three criteria
Dear Arty, Run a pivot table and make the count of Passed, Failed on the specific date by removing the N/A from the filters. I hope you know how to run the pivot table. Regards Ahmed Bawazir On Thu, Sep 15, 2011 at 7:09 PM, ArtySin kenstrain...@gmail.com wrote: Hi I have two columns as below using Excel 2000 (unfortunately, corporate no spend policy): These columns have been exported from a database and appear as the example below. A B Status Date Run Passed 22/08/2011 Failed 22/08/2011 Failed 23/08/2011 Passed 22/08/2011 Passed 23/08/2011 N/A 22/08/2011 N/A 22/08/2011 Passed 22/08/2011 N/A 23/08/2011 Failed 23/08/2011 What I want to do is add up the number of times either passed or failed appears against a date but do not want to include the N/A. For example to count up the total of passed or failed for the 22/08/2011 would give a result of 4 I've tried =SUMPRODUCT(--($A$2:$A$11=A2)--($A$2:$A$11=A3)*--($B$2:$B $11=B2)) and various countifs with AND statements but I can't get this to work. Many thanks ArtySin -- -- 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Ahmed Bawazir *احمد باوزير* -- -- 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Two columns and three criteria
Dear Arty,
Please try it and see attached sheet..
=SUMPRODUCT(--($A$4:$A$13={Passed,Failed})*(B4:B13=H6))
--
Thanks regards,
Noorain Ansari
*http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/
*http://noorain-ansari.blogspot.com/* http://noorain-ansari.blogspot.com/
On Thu, Sep 15, 2011 at 9:39 PM, ArtySin kenstrain...@gmail.com wrote:
Hi
I have two columns as below using Excel 2000 (unfortunately, corporate
no spend policy): These columns have been exported from a database and
appear as the example below.
A B
Status Date Run
Passed 22/08/2011
Failed 22/08/2011
Failed 23/08/2011
Passed 22/08/2011
Passed 23/08/2011
N/A 22/08/2011
N/A 22/08/2011
Passed 22/08/2011
N/A 23/08/2011
Failed 23/08/2011
What I want to do is add up the number of times either passed or
failed appears against a date but do not want to include the N/A. For
example to count up the total of passed or failed for the 22/08/2011
would give a result of 4
I've tried =SUMPRODUCT(--($A$2:$A$11=A2)--($A$2:$A$11=A3)*--($B$2:$B
$11=B2)) and various countifs with AND statements but I can't get
this to work.
Many thanks
ArtySin
--
--
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
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
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
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
solution.xls
Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Two columns and three criteria
Hi ArtySin, Refer the attachment file for details. Hope That Helps! --- Ms.Exl.Learner --- On Thu, Sep 15, 2011 at 9:39 PM, ArtySin kenstrain...@gmail.com wrote: Hi I have two columns as below using Excel 2000 (unfortunately, corporate no spend policy): These columns have been exported from a database and appear as the example below. A B Status Date Run Passed 22/08/2011 Failed 22/08/2011 Failed 23/08/2011 Passed 22/08/2011 Passed 23/08/2011 N/A 22/08/2011 N/A 22/08/2011 Passed 22/08/2011 N/A 23/08/2011 Failed 23/08/2011 What I want to do is add up the number of times either passed or failed appears against a date but do not want to include the N/A. For example to count up the total of passed or failed for the 22/08/2011 would give a result of 4 I've tried =SUMPRODUCT(--($A$2:$A$11=A2)--($A$2:$A$11=A3)*--($B$2:$B $11=B2)) and various countifs with AND statements but I can't get this to work. Many thanks ArtySin -- -- 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- 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 Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Sumproduct Solution (15-Sep-2011).xls Description: MS-Excel spreadsheet
