A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Using nested IFs with CountIf



 
 
Thread Tools Display Modes
  #1  
Old May 11th 06, 04:57 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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  
Old May 11th 06, 05:23 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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.
>

  #3  
Old May 11th 06, 05:26 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default Using nested IFs with CountIf

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.
>



  #4  
Old May 11th 06, 05:50 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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  
Old May 11th 06, 05:52 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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  
Old May 11th 06, 06:46 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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  
Old May 11th 06, 09:55 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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  
Old May 12th 06, 11:32 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 12:56 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.