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.
--
Best Regards,

Beth

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


"BethB" wrote:

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.
--
Best Regards,

Beth

Alok -

Seems to be working. Thanks so much!
--
Best Regards,

Beth


"Alok" wrote:

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


"BethB" wrote:

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.
--
Best Regards,

Beth