2 Criteria Count and Sum

[abernat]

I'll explain my spreadsheet quickly. A bunch of vendors with statu
(Active or Deactive) and then a purchases column ($ amounts).

Example:
Vendor - Status - $
1 - Active - $5
2 - Active - 0
3 - Deactive - 4
4 - Active - 10


Please help me out. I believe this should be easy, but I can't figur
it out for the life of me. I'm looking to do two things:

1- Count. I want to Count if the range equals Active and purchases d
not equal zero. In the above mini-spreadsheet. I would want this coun
feature to equal 2 (Two vendors have Active status and their purchase
do not equal zero).

2- Sum. Same thing as above, just with a sum function. So if you wante
to sum the vendors that are active and purchases do not equal zero the
the answer should be 15.

Please help!!! Greatly appreciated in advance

 

[Ken Wright]

Count of Active and not 0
=SUMPRODUCT((RngA="Active")*(RngB<>0))

Sum of Active and
=SUMPRODUCT((RngA="Active")*(RngB))

Do not include headers in the ranges and ranges must be the same size

 

[Earl Kiosterud]

Abernat,

=SUMPRODUCT((B2:B5="active")*(C2:C5>0))

Or for a interactive approach, set up an autofilter. Filter on Status for
Active. Filter (custom) on $ for >0. Use the SUBTOTAL function to get a
count.

 

[abernat]

Thanks for the help, however, when I put the SUMPRODUCT formula in, i
comes up #N/A. Is it because I'm multiplying a Text value (Active) by
number (Purchases)

When I'm trying to count the # of vendors that are both Active and hav
purchases, would this SUMPRODUCT function work

 

[Ken Wright]

SUMPRODUCT works in both cases UNLESS you have any 'text' values in the
'numeric' column - hence the advice not to include headers in the ranges. As
long as the Active/NonActive column is all text and the value column is all
numeric then there should be no problem - happy to send you a demo sheet if you
want, so just mail me and I'll attach one to the reply if you like

ken.wright at ntlworld.com

 

[Frank Kabel]

Hi
try
=SUMPRODUCT((B2:B5="active")*(N(C2:C5)>0))