If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




Using nested IFs with CountIf
Can anyone help me I am trying to use the nested IF formula to add more than
one criteria prior to counting the number of instance a value appears in a table. A B C 2 milk must Com 3 bread maybe Part 4 cheese never None 5 milk maybe Com 6 curry must Part 7 bread must None So, I am trying to find the number of instances milk appears in the table, which are "must" and "com". Then I am trying to count the number of instances milk appears with "maybe" and "com" etc. The result will produce an anaylysis of the entries for all the types in column A. The variances contained in column A is small approx 6 types, so will enter the criteria into the results table like so: The following table is for results which contain "Com" Must Maybe Never Milk 10 5 2 Bread 4 2 1 etc The following table is for results which contain "Part" Must Maybe Never Milk 6 2 1 Bread 3 3 0 etc I hope this is clear otherwise let me know, and thanks in anticipation. 
Ads 
#2




Using nested IFs with CountIf
You can craft a table using array formulas. Following that example, if we
create the "com" table at E2, with the products in row E and the "must", "maybe" and never in row 2, you can use this array formula in F3 =SUM(($A$2:$A$7=$E3)*($B$2:$B$7=F$2)*($C$2:$C$7="com")) As this is an array formula, you have to enter it with Ctrl+Shift+enter. You can then copy it to the other fields of the table, and the references will follow the axis. You may need to change the ranges to fit your needs, or even better give them a name and use it on the formula. For the Part table, just substitute the final part of the formula, where "com" is hardcoded. Hope this helps, Miguel. "Excel novice" wrote: > Can anyone help me I am trying to use the nested IF formula to add more than > one criteria prior to counting the number of > > instance a value appears in a table. > > A B C > 2 milk must Com > 3 bread maybe Part > 4 cheese never None > 5 milk maybe Com > 6 curry must Part > 7 bread must None > > So, I am trying to find the number of instances milk appears in the table, > which are "must" and "com". Then I am trying to count the number of instances > milk appears with "maybe" and "com" etc. The result will produce an anaylysis > of the entries for all the types in column A. The variances contained in > column A is small approx 6 types, so will enter the criteria into the results > table like so: > > The following table is for results which contain "Com" > Must Maybe Never > Milk 10 5 2 > Bread 4 2 1 > etc > > The following table is for results which contain "Part" > Must Maybe Never > Milk 6 2 1 > Bread 3 3 0 > etc > > I hope this is clear otherwise let me know, and thanks in anticipation. > 
#4




Using nested IFs with CountIf
Hi Miguel,
Thanks for your reply, however does this count the number of occurances of the criteria in each or sum the total? "Miguel Zapico" wrote: > You can craft a table using array formulas. Following that example, if we > create the "com" table at E2, with the products in row E and the "must", > "maybe" and never in row 2, you can use this array formula in F3 > =SUM(($A$2:$A$7=$E3)*($B$2:$B$7=F$2)*($C$2:$C$7="com")) > As this is an array formula, you have to enter it with Ctrl+Shift+enter. > You can then copy it to the other fields of the table, and the references > will follow the axis. > You may need to change the ranges to fit your needs, or even better give > them a name and use it on the formula. For the Part table, just substitute > the final part of the formula, where "com" is hardcoded. > > Hope this helps, > Miguel. > > "Excel novice" wrote: > > > Can anyone help me I am trying to use the nested IF formula to add more than > > one criteria prior to counting the number of > > > > instance a value appears in a table. > > > > A B C > > 2 milk must Com > > 3 bread maybe Part > > 4 cheese never None > > 5 milk maybe Com > > 6 curry must Part > > 7 bread must None > > > > So, I am trying to find the number of instances milk appears in the table, > > which are "must" and "com". Then I am trying to count the number of instances > > milk appears with "maybe" and "com" etc. The result will produce an anaylysis > > of the entries for all the types in column A. The variances contained in > > column A is small approx 6 types, so will enter the criteria into the results > > table like so: > > > > The following table is for results which contain "Com" > > Must Maybe Never > > Milk 10 5 2 > > Bread 4 2 1 > > etc > > > > The following table is for results which contain "Part" > > Must Maybe Never > > Milk 6 2 1 > > Bread 3 3 0 > > etc > > > > I hope this is clear otherwise let me know, and thanks in anticipation. > > 
#5




Using nested IFs with CountIf
Hi Bob,
thanks for your post  I am not firmilar with the sumproduct formula. Will this add or count the number of instances the criteria Com, AND Milk AND must? Thanks, Martin "Bob Phillips" wrote: > Something like > > =SUMPRODUCT((Sheet1!$C$2:$C$200="Com"),(Sheet1!$A$2:$A$200=$A2),(Sheet > 1!$B$2:$B$200=B$1)) > > etc. assuming that A2 holds Milk, B1 holds Must etc. > >  > HTH > > Bob Phillips > > (remove xxx from email address if mailing direct) > > "Excel novice" <Excel > wrote in message > ... > > Can anyone help me I am trying to use the nested IF formula to add more > than > > one criteria prior to counting the number of > > > > instance a value appears in a table. > > > > A B C > > 2 milk must Com > > 3 bread maybe Part > > 4 cheese never None > > 5 milk maybe Com > > 6 curry must Part > > 7 bread must None > > > > So, I am trying to find the number of instances milk appears in the table, > > which are "must" and "com". Then I am trying to count the number of > instances > > milk appears with "maybe" and "com" etc. The result will produce an > anaylysis > > of the entries for all the types in column A. The variances contained in > > column A is small approx 6 types, so will enter the criteria into the > results > > table like so: > > > > The following table is for results which contain "Com" > > Must Maybe Never > > Milk 10 5 2 > > Bread 4 2 1 > > etc > > > > The following table is for results which contain "Part" > > Must Maybe Never > > Milk 6 2 1 > > Bread 3 3 0 > > etc > > > > I hope this is clear otherwise let me know, and thanks in anticipation. > > > > > 
#6




Using nested IFs with CountIf
It will count, there was nothing to add that I could see in your original
post.  HTH Bob Phillips (remove xxx from email address if mailing direct) "Excel novice" > wrote in message ... > Hi Bob, > thanks for your post  I am not firmilar with the sumproduct formula. Will > this add or count the number of instances the criteria Com, AND Milk AND must? > > Thanks, > Martin > > "Bob Phillips" wrote: > > > Something like > > > > =SUMPRODUCT((Sheet1!$C$2:$C$200="Com"),(Sheet1!$A$2:$A$200=$A2),(Sheet > > 1!$B$2:$B$200=B$1)) > > > > etc. assuming that A2 holds Milk, B1 holds Must etc. > > > >  > > HTH > > > > Bob Phillips > > > > (remove xxx from email address if mailing direct) > > > > "Excel novice" <Excel > wrote in message > > ... > > > Can anyone help me I am trying to use the nested IF formula to add more > > than > > > one criteria prior to counting the number of > > > > > > instance a value appears in a table. > > > > > > A B C > > > 2 milk must Com > > > 3 bread maybe Part > > > 4 cheese never None > > > 5 milk maybe Com > > > 6 curry must Part > > > 7 bread must None > > > > > > So, I am trying to find the number of instances milk appears in the table, > > > which are "must" and "com". Then I am trying to count the number of > > instances > > > milk appears with "maybe" and "com" etc. The result will produce an > > anaylysis > > > of the entries for all the types in column A. The variances contained in > > > column A is small approx 6 types, so will enter the criteria into the > > results > > > table like so: > > > > > > The following table is for results which contain "Com" > > > Must Maybe Never > > > Milk 10 5 2 > > > Bread 4 2 1 > > > etc > > > > > > The following table is for results which contain "Part" > > > Must Maybe Never > > > Milk 6 2 1 > > > Bread 3 3 0 > > > etc > > > > > > I hope this is clear otherwise let me know, and thanks in anticipation. > > > > > > > > > 
#7




Using nested IFs with CountIf
Bob,
I have tried this is again in a fresh sheet, but still get the answer "0". The table currently looks like (with the first milk being in the A2 cell): milk must Com bread maybe Part cheese never None milk maybe Com curry must Part bread must None milk must com milk maybe com I pasted your formula into a cell and nothing happened apart from 0. Sorry to bother you again, but any ideas. Martin "Bob Phillips" wrote: > It will count, there was nothing to add that I could see in your original > post. > >  > HTH > > Bob Phillips > > (remove xxx from email address if mailing direct) > > "Excel novice" > wrote in message > ... > > Hi Bob, > > thanks for your post  I am not firmilar with the sumproduct formula. Will > > this add or count the number of instances the criteria Com, AND Milk AND > must? > > > > Thanks, > > Martin > > > > "Bob Phillips" wrote: > > > > > Something like > > > > > > > =SUMPRODUCT((Sheet1!$C$2:$C$200="Com"),(Sheet1!$A$2:$A$200=$A2),(Sheet > > > 1!$B$2:$B$200=B$1)) > > > > > > etc. assuming that A2 holds Milk, B1 holds Must etc. > > > > > >  > > > HTH > > > > > > Bob Phillips > > > > > > (remove xxx from email address if mailing direct) > > > > > > "Excel novice" <Excel > wrote in message > > > ... > > > > Can anyone help me I am trying to use the nested IF formula to add > more > > > than > > > > one criteria prior to counting the number of > > > > > > > > instance a value appears in a table. > > > > > > > > A B C > > > > 2 milk must Com > > > > 3 bread maybe Part > > > > 4 cheese never None > > > > 5 milk maybe Com > > > > 6 curry must Part > > > > 7 bread must None > > > > > > > > So, I am trying to find the number of instances milk appears in the > table, > > > > which are "must" and "com". Then I am trying to count the number of > > > instances > > > > milk appears with "maybe" and "com" etc. The result will produce an > > > anaylysis > > > > of the entries for all the types in column A. The variances contained > in > > > > column A is small approx 6 types, so will enter the criteria into the > > > results > > > > table like so: > > > > > > > > The following table is for results which contain "Com" > > > > Must Maybe Never > > > > Milk 10 5 2 > > > > Bread 4 2 1 > > > > etc > > > > > > > > The following table is for results which contain "Part" > > > > Must Maybe Never > > > > Milk 6 2 1 > > > > Bread 3 3 0 > > > > etc > > > > > > > > I hope this is clear otherwise let me know, and thanks in > anticipation. > > > > > > > > > > > > > > > > 
#8




Using nested IFs with CountIf
You have to create a results table in another sheet, such as
blank Must Maybe Never Milk Bread Cheese Curry etc. then in B2 add =SUMPRODUCT((Sheet1!$C$2:$C$200="Com"), (Sheet1!$A$2:$A$200=$A2), (Sheet1!$B$2:$B$200=B$1)) and copy down and across  HTH Bob Phillips (remove xxx from email address if mailing direct) "Excel novice" > wrote in message ... > Bob, > I have tried this is again in a fresh sheet, but still get the answer "0". > The table currently looks like (with the first milk being in the A2 cell): > > milk must Com > bread maybe Part > cheese never None > milk maybe Com > curry must Part > bread must None > milk must com > milk maybe com > > I pasted your formula into a cell and nothing happened apart from 0. > > Sorry to bother you again, but any ideas. > > Martin > > "Bob Phillips" wrote: > > > It will count, there was nothing to add that I could see in your original > > post. > > > >  > > HTH > > > > Bob Phillips > > > > (remove xxx from email address if mailing direct) > > > > "Excel novice" > wrote in message > > ... > > > Hi Bob, > > > thanks for your post  I am not firmilar with the sumproduct formula. Will > > > this add or count the number of instances the criteria Com, AND Milk AND > > must? > > > > > > Thanks, > > > Martin > > > > > > "Bob Phillips" wrote: > > > > > > > Something like > > > > > > > > > > =SUMPRODUCT((Sheet1!$C$2:$C$200="Com"),(Sheet1!$A$2:$A$200=$A2),(Sheet > > > > 1!$B$2:$B$200=B$1)) > > > > > > > > etc. assuming that A2 holds Milk, B1 holds Must etc. > > > > > > > >  > > > > HTH > > > > > > > > Bob Phillips > > > > > > > > (remove xxx from email address if mailing direct) > > > > > > > > "Excel novice" <Excel > wrote in message > > > > ... > > > > > Can anyone help me I am trying to use the nested IF formula to add > > more > > > > than > > > > > one criteria prior to counting the number of > > > > > > > > > > instance a value appears in a table. > > > > > > > > > > A B C > > > > > 2 milk must Com > > > > > 3 bread maybe Part > > > > > 4 cheese never None > > > > > 5 milk maybe Com > > > > > 6 curry must Part > > > > > 7 bread must None > > > > > > > > > > So, I am trying to find the number of instances milk appears in the > > table, > > > > > which are "must" and "com". Then I am trying to count the number of > > > > instances > > > > > milk appears with "maybe" and "com" etc. The result will produce an > > > > anaylysis > > > > > of the entries for all the types in column A. The variances contained > > in > > > > > column A is small approx 6 types, so will enter the criteria into the > > > > results > > > > > table like so: > > > > > > > > > > The following table is for results which contain "Com" > > > > > Must Maybe Never > > > > > Milk 10 5 2 > > > > > Bread 4 2 1 > > > > > etc > > > > > > > > > > The following table is for results which contain "Part" > > > > > Must Maybe Never > > > > > Milk 6 2 1 > > > > > Bread 3 3 0 > > > > > etc > > > > > > > > > > I hope this is clear otherwise let me know, and thanks in > > anticipation. > > > > > > > > > > > > > > > > > > > > > > > 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Creating a nested COUNTIF????  Simon Lloyd  Excel Worksheet Functions  8  September 7th 05 02:30 AM 
Nested Countif  wayne75  Excel Worksheet Functions  2  June 3rd 05 04:30 PM 
COUNTIF and Nested Functions?  Larry Novida  Excel Worksheet Functions  3  May 29th 05 07:05 PM 
Countif Function Nested  Angi  Excel Discussion (Misc queries)  7  May 4th 05 07:04 PM 
Countif  Countif  maswinney  Excel Worksheet Functions  3  November 16th 04 12:06 AM 