View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
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.