Using nested IFs with CountIf

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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)
 
Hi Miguel,

Thanks for your reply, however does this count the number of occurances of
the criteria in each or sum the total?
 
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
 
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)
 
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
 
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count Vaules in a range that appear in another range 5
Sum select values from a list 4
sumif neighbouring cell 7
SUMIF/AND combination? 2
Using the results of formulae between different spreadsheets. 1
nested IF without repeating the condition? 1
Eating in the Fifties 14
Excel Need Countifs Formula Help 0

Back
Top