Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a nested COUNTIF???? | Excel Worksheet Functions | |||
Nested Countif | Excel Worksheet Functions | |||
COUNTIF and Nested Functions? | Excel Worksheet Functions | |||
Countif Function -Nested | Excel Discussion (Misc queries) | |||
Countif - Countif | Excel Worksheet Functions |