Multiple Criteria IF Nesting

B

BethB

I have the need to count instances based on multiple criteria (3, to be
exact). I already have successfully done a formula based on 2 criteria, but I
am not getting the syntax right, or maybe just not using the right formula at
all to get the 3rd criteria selected out. The table of info I am culling
information is set up as shown below:

Part Part# Status Ship To Ship Date Exchange Invoice $$
Widgets 075 Shipped ABC Co 5/15/05 N/A 20
Bobbins 093 On Hold Acme Inc 3/20/05 Widget 10
Gadgets 024 Shipped Smith Co. 8/10/04 N/A 25

The spreadsheet containing the successful results thus far is set up thusly:

Part PN Shipped Exch Sold In House
Widgets 075 1 0 0 (Here's where the
problem lies)

For the shipped, exchanged, and sold, I got the results using an
aggregate function, as follows:
{Count(IF((a2:a1500="widgets")*(c2:c1500="Shipped"),g2:g1500))}

For In House, however, I need to count instances where the part =
"Widgets", Status <> "Shipped", AND Ship To <>"Acme Inc."

Any help/advice would be much appreciated.
 
A

Alok

The following should do it

=Count(IF((a2:a1500="widgets")*(c2:c1500<>"Not Shipped")*(E2:E1500<>"Acme
Inc"),g2:g1500))

This should be array entered.

Alok Joshi
 

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

Top