# Countif only if another column is not blank...

This is a discussion on Countif only if another column is not blank... within the Excel Questions forums, part of the Question Forums category; Hello, I have a list of parts in column A that is about 200 rows or so. There are only ...

1. ## Countif only if another column is not blank...

Hello,

I have a list of parts in column A that is about 200 rows or so. There are only a dozen parts or so that repeat multiple times. I then have about 20 columns of data for each part. Column K is "Customer".

What I need to do is count the number of each part in column A. That was done with a simple countif formula. Now I need to count each part in column A only if a cumster name is listed column K (this column is left blank until a customer is added). I can't seem to get this to work. Let me know if you need me to be more specific.

Thanks!

2. ## Re: Countif only if another column is not blank...

if the data was in a1:a200 and the customer name was in column k, something like (in example, enter in l1) =if(k1<>"",countif(\$a\$1:\$a\$200,a1),"") would count how many times the value in a happens in the entire range only if there were a customer name...is that what you were looking for?

3. ## Re: Countif only if another column is not blank...

If you're doing the count by product in some sort of summary table (ie 20 rows -- 1 per product) I would first suggest you try using a Pivot Table.

Failing that you can use a Sumproduct.

If we assume in A210 you have the first product to summise:

B210:=SUMPRODUCT(--(\$A\$1:\$A\$200=\$A210),--(\$K\$1:\$K\$200<>""))

The above would count the number of times product as specified in A210 appears in range A1:A200 where Customer is not blank.

4. ## Re: Countif only if another column is not blank...

DonkeyOte...Could you explain the sumproduct formula you used above. I often see that used and it is obviously more efficient however i truly don't understand how it works. Is there a brief explanation of that method? Thanks.

5. ## Re: Countif only if another column is not blank...

That didn't seem to work. It only returned the numer of times the part appears in colunm A. Here is an example:

A K
Part Customer
Widget A XYZ Corp.
Widget B
Widget A
Widget B Joe Smith
Widget A ABC Corp
Widget C

There are three "Widget A" with two customers listed. I need excel to only count the two parts with the customer listed.

So in this exmaple the result shold be:

Widget A 2
Widget B 1
Widget C 0

Hopes this helps.

Thanks!

6. ## Re: Countif only if another column is not blank...

EB08,

Bob's page here is a good read on the subject:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

In this specific case here is an example with smaller sample:

Sheet4

 A K 1 Product Customer 2 1 A 3 2 B 4 3 5 4 D 6 1 7 2 A 8 3 9 4 A 10 1 11 1 C 12 1 D 13 1 14 2 A 15 3 A 16 4 17 4 F 18 5 A 19 1 F 20 2 21 22 Product 23 1 4

 Cell Formula K23 =SUMPRODUCT(--(\$A\$1:\$A\$20=\$A23),--(\$K\$1:\$K\$20<>""))

Excel tables to the web >> Excel Jeanie HTML 4

Sumproduct is calculated as follows:

SPA_121208_201440

 A B C D 1 Formula Location: Sheet4'!\$K\$23 2 Formula: =SUMPRODUCT(--(\$A\$1:\$A\$20=\$A23),--(\$K\$1:\$K\$20<>"")) 3 Result: 4 4 Delimiter(s): ,*; 5 Component Part(s): "--(\$A\$1:\$A\$20=\$A23)" "--(\$K\$1:\$K\$20<>"")" 6 Array Row(s): 20 20 7 Array Column(s): 1 1 8 9 Calculation: Result(s) Result(s) Total(s) 10 1 0 1 0 11 2 1 1 1 12 3 0 1 0 13 4 0 0 0 14 5 0 1 0 15 6 1 0 0 16 7 0 1 0 17 8 0 0 0 18 9 0 1 0 19 10 1 0 0 20 11 1 1 1 21 12 1 1 1 22 13 1 0 0 23 14 0 1 0 24 15 0 1 0 25 16 0 0 0 26 17 0 1 0 27 18 0 1 0 28 19 1 1 1 29 20 0 0 0

Excel tables to the web >> Excel Jeanie HTML 4

7. ## Re: Countif only if another column is not blank...

I did in l1 =if(k1<>"",a1,"") and copied it down then created a list of all products...for example in m1 i would have the product apple. in n1 i put =countif(\$l\$1:\$l\$200,n1)...i believe it got the answer you would be looking for but there is probably a more efficient way out there.

9. ## Re: Countif only if another column is not blank...

Thanks DoneyOte! That works if I use specific cell references (A1:A200), but I would like to use just column A (A:A) since this spreadsheet is constantly growing and shrinking as data is added and deleted.

If I use the formula:

=SUMPRODUCT(--(A:A="Cidermill"),--(K:K<>""))

I get a #NUM! error. What am I doing wrong?

Thanks!

10. ## Re: Countif only if another column is not blank...

Pre-XL07 you can't use entire column references with Sumproduct, that said you should not set your ranges to be too large anyway as it will impact performance... give yourself some breathing room for sure but the larger the ranges you use the greater the impact the formulae will have on your file as a whole in terms of performance. You could look into using dynamic named ranges but without knowing the particulars of your setup it's hard to comment (in terms of blanks interspersed in ranges etc...) -- if you do a search on dynamic named ranges you should find plenty of examples if you opt to go that route.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•