Counting Results after Multiple criteria are filled.

**Danieljfern** · 06-10-2011, 09:20 AM

Hi,

Hopefully someone can help.

I would like some help in being able to count each occurence of 0 in one column, after a text criteria has been filled in another.

I.e. in one column I have a supplier code (Column B) (each supplier code is listed by each of products they sell to me (Column A)), and in another I have a count of the number of cells of data I still need from them for each product (Column CU). I want to count the number of times each supplier code returns a "0" in Column CU.

The attached worksheet should illustrate.

All of these rows are returning zero's in column CU, therefore Supplier Code TD106 (column B) has 4 instances of returning a zero (Column CU) and TB026 has 6.

is there a formula I can use to lookup against the supplier code ColumnB and count the zeros. This is actually a huge file, and the list contains many different values in CU, I just want to count the zeros.

Thanks

**NBVC** · 06-10-2011, 09:40 AM

So if you have a separate list of unique suppliers...

e.g. in Sheet2, starting at A2, then in B2 try formula:

=SUMPRODUCT(--(Sheet1!$B$2:$B$100=A2),--(Sheet1!$CU$2:$CU$100=0))

adjust ranges to suit and copy down.

You can also use a quicker Pivot Table and add column CU to the Report Filter so that you can select to view o only...

See attached.

**Danieljfern** · 06-10-2011, 11:02 AM

Hi NBVC,

I ended up using the Pivot table which works fine, although I didn't really want to have to update my table every time I added data, Beggers can't be choosers though eh?.

I had tried the Sumproduct formula but I get a #NUM! error, maybe because my results (where i'm entering the formula) are on a seperate workbook. When i tried this initially I thought maybe the issue was that the values in column A are text, maybe I'm wrong, needless to say, it didn't work

Also I don't know how to mark as [SOLVED] as this is my first ever forum post.

Thanks.

**NBVC** · 06-10-2011, 11:06 AM

You might get the #NUM error if you are trying to apply whole ranges to SUMPRODUCT. It is not allowed in XL2003 or earlier.. redefine the range and make it smaller rather than larger.. because SUMPRODUCT is also inefficient when covering large ranges...

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save