software

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.

ABC
2milkmustCom
3breadmaybePart
4cheeseneverNone
5 milkmaybeCom
6 currymustPart
7 breadmustNone

So, I am trying to find the number of instances milk appears in the table,
which are quot;mustquot; and quot;comquot;. Then I am trying to count the number of instances
milk appears with quot;maybequot; and quot;comquot; 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 quot;Comquot;
Must MaybeNever
Milk 1052
Bread421
etc

The following table is for results which contain quot;Partquot;
Must MaybeNever
Milk 621
Bread330
etc

I hope this is clear otherwise let me know, and thanks in anticipation.You can craft a table using array formulas. Following that example, if we
create the quot;comquot; table at E2, with the products in row E and the quot;mustquot;,
quot;maybequot; 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=quot;comquot;))
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 quot;comquot; is hardcoded.

Hope this helps,
Miguel.

quot;Excel novicequot; wrote:

gt; Can anyone help me I am trying to use the nested IF formula to add more than
gt; one criteria prior to counting the number of
gt;
gt; instance a value appears in a table.
gt;
gt; ABC
gt; 2milkmustCom
gt; 3breadmaybePart
gt; 4cheeseneverNone
gt; 5 milkmaybeCom
gt; 6 currymustPart
gt; 7 breadmustNone
gt;
gt; So, I am trying to find the number of instances milk appears in the table,
gt; which are quot;mustquot; and quot;comquot;. Then I am trying to count the number of instances
gt; milk appears with quot;maybequot; and quot;comquot; etc. The result will produce an anaylysis
gt; of the entries for all the types in column A. The variances contained in
gt; column A is small approx 6 types, so will enter the criteria into the results
gt; table like so:
gt;
gt; The following table is for results which contain quot;Comquot;
gt; Must MaybeNever
gt; Milk 1052
gt; Bread421
gt; etc
gt;
gt; The following table is for results which contain quot;Partquot;
gt; Must MaybeNever
gt; Milk 621
gt; Bread330
gt; etc
gt;
gt; I hope this is clear otherwise let me know, and thanks in anticipation.
gt;

Something like

=SUMPRODUCT(--(Sheet1!$C$2:$C$200=quot;Comquot;),--(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)

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

Thanks for your reply, however does this count the number of occurances of
the criteria in each or sum the total?

quot;Miguel Zapicoquot; wrote:

gt; You can craft a table using array formulas. Following that example, if we
gt; create the quot;comquot; table at E2, with the products in row E and the quot;mustquot;,
gt; quot;maybequot; and never in row 2, you can use this array formula in F3
gt; =SUM(--($A$2:$A$7=$E3)*--($B$2:$B$7=F$2)*--($C$2:$C$7=quot;comquot;))
gt; As this is an array formula, you have to enter it with Ctrl Shift enter.
gt; You can then copy it to the other fields of the table, and the references
gt; will follow the axis.
gt; You may need to change the ranges to fit your needs, or even better give
gt; them a name and use it on the formula. For the Part table, just substitute
gt; the final part of the formula, where quot;comquot; is hardcoded.
gt;
gt; Hope this helps,
gt; Miguel.
gt;
gt; quot;Excel novicequot; wrote:
gt;
gt; gt; Can anyone help me I am trying to use the nested IF formula to add more than
gt; gt; one criteria prior to counting the number of
gt; gt;
gt; gt; instance a value appears in a table.
gt; gt;
gt; gt; ABC
gt; gt; 2milkmustCom
gt; gt; 3breadmaybePart
gt; gt; 4cheeseneverNone
gt; gt; 5 milkmaybeCom
gt; gt; 6 currymustPart
gt; gt; 7 breadmustNone
gt; gt;
gt; gt; So, I am trying to find the number of instances milk appears in the table,
gt; gt; which are quot;mustquot; and quot;comquot;. Then I am trying to count the number of instances
gt; gt; milk appears with quot;maybequot; and quot;comquot; etc. The result will produce an anaylysis
gt; gt; of the entries for all the types in column A. The variances contained in
gt; gt; column A is small approx 6 types, so will enter the criteria into the results
gt; gt; table like so:
gt; gt;
gt; gt; The following table is for results which contain quot;Comquot;
gt; gt; Must MaybeNever
gt; gt; Milk 1052
gt; gt; Bread421
gt; gt; etc
gt; gt;
gt; gt; The following table is for results which contain quot;Partquot;
gt; gt; Must MaybeNever
gt; gt; Milk 621
gt; gt; Bread330
gt; gt; etc
gt; gt;
gt; gt; I hope this is clear otherwise let me know, and thanks in anticipation.
gt; gt;

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

quot;Bob Phillipsquot; wrote:

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

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)

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

milkmustCom
breadmaybePart
cheeseneverNone
milkmaybeCom
currymustPart
breadmustNone
milkmustcom
milkmaybecom

I pasted your formula into a cell and nothing happened apart from 0.

Sorry to bother you again, but any ideas.

Martin

quot;Bob Phillipsquot; wrote:

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

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=quot;Comquot;),
--(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)

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